Master Database Schema - MediTech Hospital Information System

Document Version: 1.0
Date: 28 สิงหาคม 2025
Prepared by: Claude Code - Senior Medical Technology Systems Analyst
System Integration: All 32 MediTech HIS Modules
Technology Stack: Nest.js + PostgreSQL 15+ + Prisma ORM + Redis 7+

Executive Summary

This Master Database Schema serves as the definitive database design reference for the entire MediTech Hospital Information System, integrating all 32 modules into a cohesive, scalable, and healthcare-compliant database architecture. The schema ensures seamless data flow between modules while maintaining individual system autonomy and supporting concurrent development.

Key Design Principles:

Unified Foundation: Shared core tables across all modules
Healthcare Compliance: PDPA, ISO 27001, medical record retention standards
Performance Optimized: 100+ concurrent users with <500ms response times
API-First Design: RESTful architecture with real-time WebSocket support
Security-Centric: Role-based access control with digital signatures
Scalable Architecture: Partitioning and indexing strategies for growth

SECTION 1: FOUNDATION TABLES

Core Shared Tables - The Foundation of All Modules

These tables form the cornerstone of the MediTech system, providing unified identity and organizational structure across all modules.

1.1 Master User Management

-- Core user table for all hospital staff
CREATE TABLE users (
    -- Primary Key
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

    -- Unique Identifiers
    employee_id VARCHAR(20) UNIQUE NOT NULL,
    national_id VARCHAR(13) UNIQUE NOT NULL,
    username VARCHAR(50) UNIQUE NOT NULL,

    -- Authentication
    password_hash VARCHAR(255) NOT NULL,
    password_salt VARCHAR(32) NOT NULL,
    password_expires_at TIMESTAMP WITH TIME ZONE,

    -- 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),

    -- Contact Information
    email VARCHAR(100) UNIQUE NOT NULL,
    phone VARCHAR(15),
    mobile VARCHAR(15),
    emergency_contact VARCHAR(100),
    emergency_phone VARCHAR(15),

    -- Organization Information
    department_id UUID NOT NULL REFERENCES departments(id),
    position_id UUID NOT NULL REFERENCES positions(id),
    employee_type VARCHAR(20) CHECK (employee_type IN ('permanent', 'contract', 'temporary', 'volunteer')) DEFAULT 'permanent',
    employment_date DATE NOT NULL,
    termination_date DATE,

    -- Medical License (for healthcare professionals)
    medical_license VARCHAR(20),
    license_expiry_date DATE,
    specialty VARCHAR(100),

    -- System Preferences
    preferred_language VARCHAR(5) DEFAULT 'th' CHECK (preferred_language IN ('th', 'en')),
    timezone VARCHAR(50) DEFAULT 'Asia/Bangkok',

    -- Security Settings
    mfa_enabled BOOLEAN DEFAULT FALSE,
    mfa_secret VARCHAR(64),
    account_locked BOOLEAN DEFAULT FALSE,
    locked_until TIMESTAMP WITH TIME ZONE,
    failed_login_attempts INTEGER DEFAULT 0,
    force_password_change BOOLEAN DEFAULT FALSE,

    -- Session Management
    last_login_at TIMESTAMP WITH TIME ZONE,
    last_activity_at TIMESTAMP WITH TIME ZONE,
    current_session_id VARCHAR(255),

    -- 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,

    -- Timestamps
    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),

    -- Full-text search
    search_vector tsvector GENERATED ALWAYS AS (
        to_tsvector('thai', 
            first_name_thai || ' ' || last_name_thai || ' ' ||
            COALESCE(first_name_eng, '') || ' ' || COALESCE(last_name_eng, '') || ' ' ||
            COALESCE(employee_id, '') || ' ' || COALESCE(username, '') || ' ' ||
            COALESCE(email, '') || ' ' || COALESCE(nickname, '')
        )
    ) STORED
);

1.2 Patient Demographics (Master Patient Index)

-- Master patient demographics shared across all clinical modules
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,
    passport_id VARCHAR(20),

    -- 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')),

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

    -- Address Information
    registered_address TEXT,
    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),

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

    -- 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,

    -- Temporary HN Management for Emergency Cases
    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),

    -- 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
);

1.3 Hospital Organization Structure

-- Hospital departments for all modules
CREATE TABLE departments (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

    -- Department Information
    code VARCHAR(10) UNIQUE NOT NULL,
    name_thai VARCHAR(100) NOT NULL,
    name_eng VARCHAR(100),
    description TEXT,

    -- Hierarchy
    parent_department_id UUID REFERENCES departments(id),
    level INTEGER DEFAULT 1,
    sort_order INTEGER DEFAULT 0,

    -- Contact Information
    phone VARCHAR(15),
    email VARCHAR(100),
    location VARCHAR(200),

    -- Settings
    cost_center VARCHAR(20),
    budget_code VARCHAR(20),

    -- Department Type for different modules
    department_type VARCHAR(30) CHECK (department_type IN (
        'clinical', 'diagnostic', 'support', 'administration', 'financial', 'ancillary'
    )) NOT NULL,

    -- Service Categories
    service_categories JSONB, -- ['inpatient', 'outpatient', 'emergency', 'diagnostic']

    -- Operating Hours
    operating_hours JSONB, -- Schedule information

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

    -- Timestamps
    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)
);

-- Hospital positions/roles
CREATE TABLE positions (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

    -- Position Information
    code VARCHAR(20) UNIQUE NOT NULL,
    name_thai VARCHAR(100) NOT NULL,
    name_eng VARCHAR(100),
    description TEXT,

    -- Position Details
    category VARCHAR(50) NOT NULL, -- medical, nursing, admin, support, technical
    level INTEGER DEFAULT 1,
    requires_medical_license BOOLEAN DEFAULT FALSE,

    -- Permission Templates
    default_permissions_template_id UUID,

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

    -- Timestamps
    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)
);

-- Clinics within departments
CREATE TABLE clinics (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

    -- Clinic Information
    code VARCHAR(20) UNIQUE NOT NULL,
    name_thai VARCHAR(100) NOT NULL,
    name_eng VARCHAR(100),
    description TEXT,

    -- Organization
    department_id UUID NOT NULL REFERENCES departments(id),

    -- Location
    building VARCHAR(50),
    floor VARCHAR(10),
    room_number VARCHAR(20),

    -- Capacity and Equipment
    max_capacity INTEGER DEFAULT 1,
    equipment_list JSONB,

    -- Scheduling
    is_appointment_required BOOLEAN DEFAULT TRUE,
    allows_walk_in BOOLEAN DEFAULT FALSE,
    operating_hours JSONB,

    -- Service Types
    service_types JSONB, -- consultation, procedure, diagnostic

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

    -- Timestamps
    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)
);

1.4 Visit Management (Core Clinical Encounters)

-- Medical visits - central to all clinical modules
CREATE TABLE medical_visits (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

    -- Visit Identification
    visit_number VARCHAR(20) UNIQUE NOT NULL,
    patient_id UUID NOT NULL REFERENCES patients(id),

    -- Visit Details
    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', 'DAY_SURGERY', 'TELEMEDICINE')),

    -- Location
    department_id UUID REFERENCES departments(id),
    clinic_id UUID REFERENCES clinics(id),

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

    -- Provider Information
    primary_doctor_id UUID REFERENCES users(id),
    attending_doctor_id UUID REFERENCES users(id),
    primary_nurse_id UUID REFERENCES users(id),

    -- Visit Flow
    visit_status VARCHAR(20) DEFAULT 'registered' CHECK (visit_status IN (
        'registered', 'checked_in', 'in_progress', 'completed', 'cancelled', 'no_show'
    )),

    -- 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,

    -- Financial Information
    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,

    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)
);

SECTION 2: AUTHENTICATION & SECURITY

Role-Based Access Control System

-- System roles for all modules
CREATE TABLE roles (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

    -- Role Information
    name VARCHAR(50) UNIQUE NOT NULL,
    display_name_thai VARCHAR(100) NOT NULL,
    display_name_eng VARCHAR(100),
    description TEXT,

    -- Role Type and Hierarchy
    role_type VARCHAR(20) DEFAULT 'functional' CHECK (role_type IN ('system', 'functional', 'departmental')),
    parent_role_id UUID REFERENCES roles(id),
    level INTEGER DEFAULT 1,

    -- System Settings
    is_system_role BOOLEAN DEFAULT FALSE,
    max_users INTEGER,

    -- Security Level
    security_level INTEGER DEFAULT 1 CHECK (security_level BETWEEN 1 AND 10),
    requires_approval BOOLEAN DEFAULT FALSE,

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

    -- Timestamps
    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)
);

-- System permissions for all modules
CREATE TABLE permissions (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

    -- Permission Information
    name VARCHAR(100) UNIQUE NOT NULL, -- e.g., 'patients.view', 'prescriptions.create'
    display_name_thai VARCHAR(150) NOT NULL,
    display_name_eng VARCHAR(150),
    description TEXT,

    -- Permission Details
    module VARCHAR(50) NOT NULL, -- EMR, eMAR, CPOE, Admin, Queue, Lab, Radiology
    resource VARCHAR(50) NOT NULL, -- patients, prescriptions, users, appointments
    action VARCHAR(20) NOT NULL, -- view, create, edit, delete, approve

    -- Security Level
    security_level INTEGER DEFAULT 1 CHECK (security_level BETWEEN 1 AND 10),
    is_sensitive BOOLEAN DEFAULT FALSE,

    -- System Settings
    is_system_permission BOOLEAN DEFAULT FALSE,

    -- Conditions and Constraints
    conditions JSONB, -- additional constraints like time, IP, department

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

    -- Timestamps
    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)
);

-- User role assignments
CREATE TABLE user_roles (
    user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    role_id UUID NOT NULL REFERENCES roles(id) ON DELETE CASCADE,

    -- Assignment Information
    assigned_by UUID NOT NULL REFERENCES users(id),
    assigned_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    expires_at TIMESTAMP WITH TIME ZONE,

    -- Scope Limitations
    department_scope UUID REFERENCES departments(id),
    patient_scope VARCHAR(20), -- inpatient, outpatient, all

    -- Status
    is_active BOOLEAN DEFAULT TRUE,
    deactivated_at TIMESTAMP WITH TIME ZONE,
    deactivated_by UUID REFERENCES users(id),
    deactivation_reason TEXT,

    PRIMARY KEY (user_id, role_id)
);

-- Role permission mappings
CREATE TABLE role_permissions (
    role_id UUID NOT NULL REFERENCES roles(id) ON DELETE CASCADE,
    permission_id UUID NOT NULL REFERENCES permissions(id) ON DELETE CASCADE,

    -- Permission Settings
    granted BOOLEAN DEFAULT TRUE,
    granted_by UUID NOT NULL REFERENCES users(id),
    granted_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,

    -- Additional Constraints
    conditions JSONB,
    expires_at TIMESTAMP WITH TIME ZONE,

    -- Audit
    notes TEXT,

    PRIMARY KEY (role_id, permission_id)
);

Digital Signature System

-- Digital signatures for healthcare document compliance
CREATE TABLE digital_signatures (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

    -- Owner Information
    user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,

    -- Signature Information
    signature_name VARCHAR(100) NOT NULL,
    signature_type VARCHAR(20) NOT NULL CHECK (signature_type IN (
        'handwritten', 'digital_pen', 'pki', 'thai_id', 'biometric'
    )),

    -- Signature Data
    signature_data BYTEA,
    signature_hash VARCHAR(255) NOT NULL,
    encoding_format VARCHAR(20) DEFAULT 'base64',

    -- Certificate Information
    certificate_id UUID,
    certificate_serial VARCHAR(100),

    -- Validation Information
    is_verified BOOLEAN DEFAULT FALSE,
    verified_at TIMESTAMP WITH TIME ZONE,
    verified_by UUID REFERENCES users(id),
    verification_method VARCHAR(50),

    -- Usage Settings
    is_primary BOOLEAN DEFAULT FALSE,
    is_active BOOLEAN DEFAULT TRUE,

    -- Legal Information
    legal_name_thai VARCHAR(200),
    legal_name_eng VARCHAR(200),
    position_when_created VARCHAR(100),

    -- Security
    encryption_algorithm VARCHAR(50) DEFAULT 'SHA-256',
    key_length INTEGER,

    -- Usage Statistics
    usage_count INTEGER DEFAULT 0,
    last_used_at TIMESTAMP WITH TIME ZONE,

    -- Expiration
    expires_at TIMESTAMP WITH TIME ZONE,

    -- Status and Audit
    is_revoked BOOLEAN DEFAULT FALSE,
    revoked_at TIMESTAMP WITH TIME ZONE,
    revoked_by UUID REFERENCES users(id),
    revocation_reason TEXT,

    -- Timestamps
    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)
);

-- Document signatures for audit trail
CREATE TABLE document_signatures (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

    -- Document Reference
    document_type VARCHAR(50) NOT NULL, -- prescription, medical_certificate, soap_note, lab_report
    document_id VARCHAR(100) NOT NULL,
    document_hash VARCHAR(255),

    -- Signature Information
    signature_id UUID NOT NULL REFERENCES digital_signatures(id),
    user_id UUID NOT NULL REFERENCES users(id),

    -- Signing Details
    signed_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    signature_purpose VARCHAR(50) NOT NULL, -- approval, acknowledgment, authorization

    -- Position and Role at time of signing
    user_position VARCHAR(100),
    user_department VARCHAR(100),
    signing_authority VARCHAR(100),

    -- Signature Data
    signature_value TEXT NOT NULL,
    signature_format VARCHAR(20) DEFAULT 'pkcs7',

    -- Verification
    is_verified BOOLEAN DEFAULT FALSE,
    verified_at TIMESTAMP WITH TIME ZONE,
    verification_result JSONB,

    -- Legal Information
    legal_representative BOOLEAN DEFAULT FALSE,
    on_behalf_of VARCHAR(200),

    -- Workflow Information
    workflow_step VARCHAR(50),
    next_required_signers JSONB,

    -- Document Context
    document_version INTEGER DEFAULT 1,
    page_number INTEGER,
    coordinates JSONB,

    -- Status
    is_valid BOOLEAN DEFAULT TRUE,
    is_revoked BOOLEAN DEFAULT FALSE,
    revoked_at TIMESTAMP WITH TIME ZONE,
    revoked_by UUID REFERENCES users(id),
    revocation_reason TEXT,

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

SECTION 3: CLINICAL CORE SYSTEMS

Appointment & Queue Management Integration

-- Appointment system integrated with all clinical modules
CREATE TABLE appointments (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

    -- Appointment Identification
    appointment_number VARCHAR(20) UNIQUE NOT NULL,
    patient_id UUID NOT NULL REFERENCES patients(id),

    -- Appointment Details
    appointment_date DATE NOT NULL,
    appointment_time TIME NOT NULL,
    estimated_duration INTEGER DEFAULT 30, -- minutes

    -- Provider Information
    doctor_id UUID REFERENCES users(id),
    department_id UUID REFERENCES departments(id),
    clinic_id UUID REFERENCES clinics(id),

    -- Appointment Type
    appointment_type VARCHAR(30) NOT NULL CHECK (appointment_type IN (
        'consultation', 'follow_up', 'procedure', 'surgery', 'diagnostic', 'wellness_check'
    )),
    visit_type VARCHAR(10) CHECK (visit_type IN ('OPD', 'IPD', 'ER', 'TELEMEDICINE')),

    -- Booking Information
    booking_channel VARCHAR(20) CHECK (booking_channel IN ('staff', 'mobile_app', 'line_bot', 'kiosk', 'phone')),
    booking_source VARCHAR(100),
    booked_by_user_id UUID REFERENCES users(id),

    -- Patient Information
    chief_complaint TEXT,
    special_instructions TEXT,

    -- Package Appointments (for multi-step procedures)
    package_id UUID,
    package_sequence INTEGER,

    -- Status Management
    status VARCHAR(20) DEFAULT 'scheduled' CHECK (status IN (
        'scheduled', 'confirmed', 'checked_in', 'in_progress', 'completed', 'cancelled', 'no_show', 'rescheduled'
    )),

    -- Confirmation
    is_confirmed BOOLEAN DEFAULT FALSE,
    confirmed_at TIMESTAMP WITH TIME ZONE,
    confirmation_method VARCHAR(20), -- sms, line, email, phone

    -- Check-in Process
    checked_in_at TIMESTAMP WITH TIME ZONE,
    check_in_method VARCHAR(20), -- kiosk, staff, mobile_app

    -- Cancellation
    cancelled_at TIMESTAMP WITH TIME ZONE,
    cancelled_by UUID REFERENCES users(id),
    cancellation_reason TEXT,

    -- Rescheduling
    rescheduled_from UUID REFERENCES appointments(id),
    rescheduled_to UUID REFERENCES appointments(id),
    reschedule_reason TEXT,

    -- Financial
    estimated_cost DECIMAL(10,2),
    copayment_required DECIMAL(10,2),

    -- Integration with Visit
    medical_visit_id UUID REFERENCES medical_visits(id),

    -- Reminders
    reminder_sent BOOLEAN DEFAULT FALSE,
    reminder_count INTEGER DEFAULT 0,
    last_reminder_at TIMESTAMP WITH TIME ZONE,

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

    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)
);

-- Queue management for real-time patient flow
CREATE TABLE queues (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

    -- Queue Identification
    queue_number VARCHAR(10) NOT NULL,
    queue_date DATE NOT NULL DEFAULT CURRENT_DATE,

    -- Patient and Visit Information
    patient_id UUID NOT NULL REFERENCES patients(id),
    visit_id UUID REFERENCES medical_visits(id),
    appointment_id UUID REFERENCES appointments(id),

    -- Service Point Information
    department_id UUID NOT NULL REFERENCES departments(id),
    clinic_id UUID REFERENCES clinics(id),
    service_point VARCHAR(50), -- counter, room, station

    -- Queue Priority System
    queue_priority INTEGER DEFAULT 0,
    queue_type VARCHAR(20) DEFAULT 'normal' CHECK (queue_type IN (
        'normal', 'elderly', 'disabled', 'emergency', 'vip', 'pregnant', 'child'
    )),

    -- Queue Flow Status
    queue_status VARCHAR(20) DEFAULT 'waiting' CHECK (queue_status IN (
        'waiting', 'called', 'serving', 'completed', 'no_show', 'transferred'
    )),

    -- Timing Information
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    called_at TIMESTAMP WITH TIME ZONE,
    served_at TIMESTAMP WITH TIME ZONE,
    completed_at TIMESTAMP WITH TIME ZONE,

    -- Estimated Service Time
    estimated_wait_time INTEGER, -- minutes
    estimated_service_time INTEGER, -- minutes

    -- Queue Calling
    call_count INTEGER DEFAULT 0,
    last_called_at TIMESTAMP WITH TIME ZONE,
    called_by_user_id UUID REFERENCES users(id),

    -- Transfer and Referral
    transferred_from_queue_id UUID REFERENCES queues(id),
    transferred_to_department UUID REFERENCES departments(id),
    transfer_reason TEXT,

    -- Multi-Service Point Journey
    sequence_order INTEGER DEFAULT 1, -- for patients visiting multiple service points
    parent_queue_id UUID REFERENCES queues(id),

    -- Integration Data
    external_queue_id VARCHAR(50), -- for external systems
    integration_data JSONB,

    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

Medical Orders (CPOE) System

-- Medical orders system for all clinical modules
CREATE TABLE medical_orders (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

    -- Order Identification
    order_number VARCHAR(20) UNIQUE NOT NULL,
    visit_id UUID NOT NULL REFERENCES medical_visits(id),
    patient_id UUID NOT NULL REFERENCES patients(id),

    -- Order Classification
    order_type VARCHAR(30) NOT NULL CHECK (order_type IN (
        'medication', 'laboratory', 'radiology', 'procedure', 'consultation', 'diet', 'nursing', 'admission', 'discharge'
    )),
    order_category VARCHAR(50), -- further classification within type

    -- Provider Information
    ordered_by_user_id UUID NOT NULL REFERENCES users(id),
    ordering_department_id UUID REFERENCES departments(id),

    -- Order Details
    order_text TEXT NOT NULL, -- original order text
    structured_order JSONB, -- parsed/structured order data

    -- CPOE AI Assist Integration
    ai_parsed_order JSONB, -- AI-parsed structured data
    ai_confidence_score DECIMAL(3,2), -- 0.00 to 1.00
    ai_suggestions JSONB,
    manual_override BOOLEAN DEFAULT FALSE,

    -- Clinical Decision Support
    drug_interactions JSONB,
    allergy_alerts JSONB,
    clinical_alerts JSONB,

    -- Priority and Timing
    priority VARCHAR(20) DEFAULT 'routine' CHECK (priority IN ('stat', 'urgent', 'routine', 'prn')),
    order_date_time TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    start_date_time TIMESTAMP WITH TIME ZONE,
    stop_date_time TIMESTAMP WITH TIME ZONE,

    -- Status Management
    order_status VARCHAR(20) DEFAULT 'active' CHECK (order_status IN (
        'active', 'held', 'cancelled', 'completed', 'expired', 'discontinued'
    )),

    -- Verification and Authorization
    requires_verification BOOLEAN DEFAULT FALSE,
    verified_by_user_id UUID REFERENCES users(id),
    verified_at TIMESTAMP WITH TIME ZONE,

    requires_authorization BOOLEAN DEFAULT FALSE,
    authorized_by_user_id UUID REFERENCES users(id),
    authorized_at TIMESTAMP WITH TIME ZONE,

    -- Execution Information
    executing_department_id UUID REFERENCES departments(id),
    assigned_to_user_id UUID REFERENCES users(id),

    -- Results and Completion
    result_text TEXT,
    result_data JSONB,
    completed_at TIMESTAMP WITH TIME ZONE,
    completed_by_user_id UUID REFERENCES users(id),

    -- Modification History
    modified_from_order_id UUID REFERENCES medical_orders(id),
    modification_reason TEXT,

    -- Financial Information
    estimated_cost DECIMAL(10,2),
    actual_cost DECIMAL(10,2),

    -- External Integration
    external_order_id VARCHAR(50),
    sent_to_external_system BOOLEAN DEFAULT FALSE,
    sent_at TIMESTAMP WITH TIME ZONE,

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

    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)
);

SECTION 4: DIAGNOSTIC SYSTEMS

Laboratory Systems Integration

-- Laboratory orders from CPOE integration
CREATE TABLE lab_orders (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

    -- Order Reference
    medical_order_id UUID NOT NULL REFERENCES medical_orders(id),
    visit_id UUID NOT NULL REFERENCES medical_visits(id),
    patient_id UUID NOT NULL REFERENCES patients(id),

    -- Lab Order Details
    lab_order_number VARCHAR(20) UNIQUE NOT NULL,
    test_code VARCHAR(20) NOT NULL,
    test_name VARCHAR(200) NOT NULL,
    test_category VARCHAR(50), -- hematology, chemistry, microbiology, immunology

    -- Specimen Information
    specimen_type VARCHAR(50), -- blood, urine, stool, csf
    specimen_container VARCHAR(50),
    specimen_volume DECIMAL(8,3), -- ml

    -- Collection Details
    collection_date_time TIMESTAMP WITH TIME ZONE,
    collected_by_user_id UUID REFERENCES users(id),
    collection_site VARCHAR(100),
    fasting_required BOOLEAN DEFAULT FALSE,

    -- Processing Information
    processing_priority VARCHAR(20) DEFAULT 'routine' CHECK (processing_priority IN ('stat', 'urgent', 'routine')),
    processing_department_id UUID REFERENCES departments(id),

    -- Status Management
    lab_status VARCHAR(30) DEFAULT 'ordered' CHECK (lab_status IN (
        'ordered', 'collected', 'received', 'processing', 'completed', 'cancelled', 'rejected'
    )),

    -- Results
    result_value VARCHAR(500),
    result_unit VARCHAR(50),
    reference_range VARCHAR(200),
    abnormal_flag VARCHAR(10), -- H, L, HH, LL, A

    -- Critical Values
    is_critical_value BOOLEAN DEFAULT FALSE,
    critical_value_notified BOOLEAN DEFAULT FALSE,
    critical_value_notified_at TIMESTAMP WITH TIME ZONE,
    notified_to_user_id UUID REFERENCES users(id),

    -- Quality Control
    qc_passed BOOLEAN DEFAULT TRUE,
    qc_notes TEXT,

    -- Reporting
    reported_at TIMESTAMP WITH TIME ZONE,
    reported_by_user_id UUID REFERENCES users(id),
    verified_at TIMESTAMP WITH TIME ZONE,
    verified_by_user_id UUID REFERENCES users(id),

    -- External Lab Integration
    external_lab_id VARCHAR(50),
    sent_to_external_lab BOOLEAN DEFAULT FALSE,
    external_result_received BOOLEAN DEFAULT FALSE,

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

    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)
);

-- Radiology orders and imaging management
CREATE TABLE radiology_orders (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

    -- Order Reference
    medical_order_id UUID NOT NULL REFERENCES medical_orders(id),
    visit_id UUID NOT NULL REFERENCES medical_visits(id),
    patient_id UUID NOT NULL REFERENCES patients(id),

    -- Radiology Order Details
    radiology_order_number VARCHAR(20) UNIQUE NOT NULL,
    exam_code VARCHAR(20) NOT NULL,
    exam_name VARCHAR(200) NOT NULL,
    exam_category VARCHAR(50), -- x-ray, ct, mri, ultrasound, nuclear

    -- Clinical Information
    clinical_indication TEXT,
    clinical_history TEXT,
    contrast_required BOOLEAN DEFAULT FALSE,
    contrast_type VARCHAR(50),

    -- Scheduling
    preferred_date DATE,
    preferred_time TIME,
    scheduled_date_time TIMESTAMP WITH TIME ZONE,

    -- Equipment and Room
    modality VARCHAR(50), -- CT, MRI, X-RAY, US
    equipment_id VARCHAR(50),
    room_location VARCHAR(50),

    -- Status Management
    radiology_status VARCHAR(30) DEFAULT 'ordered' CHECK (radiology_status IN (
        'ordered', 'scheduled', 'arrived', 'in_progress', 'completed', 'cancelled', 'no_show'
    )),

    -- Acquisition Information
    acquisition_started_at TIMESTAMP WITH TIME ZONE,
    acquisition_completed_at TIMESTAMP WITH TIME ZONE,
    technologist_user_id UUID REFERENCES users(id),

    -- Image Data
    dicom_study_uid VARCHAR(100),
    image_count INTEGER DEFAULT 0,
    image_storage_path VARCHAR(500),

    -- Reporting
    preliminary_report TEXT,
    final_report TEXT,
    reported_at TIMESTAMP WITH TIME ZONE,
    radiologist_user_id UUID REFERENCES users(id),

    -- Quality and Safety
    radiation_dose DECIMAL(10,4),
    dose_unit VARCHAR(10),

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

    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)
);

SECTION 6: FINANCIAL SYSTEMS

Insurance & Rights Management (ใช้ร่วมกันทุกโมดูลการเงิน)

-- Insurance types for 43 standard insurance categories in Thailand
CREATE TABLE fin_insurance_types (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

    -- Insurance Information
    insurance_code VARCHAR(10) UNIQUE NOT NULL, -- 01-43 (43 standard insurance types)
    insurance_name_thai VARCHAR(200) NOT NULL,
    insurance_name_eng VARCHAR(200),
    insurance_category VARCHAR(50) NOT NULL, -- UC, SSO, CSMBS, Private, Cash

    -- Payer Information
    payer_organization VARCHAR(200) NOT NULL, -- NHSO, SSO, CGD, Insurance Company
    payer_code VARCHAR(20),

    -- Coverage Rules
    requires_approval BOOLEAN DEFAULT FALSE,
    requires_preauth BOOLEAN DEFAULT FALSE,
    copayment_rate DECIMAL(5,4) DEFAULT 0.0000 CHECK (copayment_rate >= 0 AND copayment_rate <= 1),
    coverage_limit DECIMAL(15,2),
    annual_limit DECIMAL(15,2),

    -- Claim Processing
    claim_format VARCHAR(20) DEFAULT 'e-claim', -- e-claim, manual, direct_billing
    api_endpoint VARCHAR(500),
    supports_realtime_verification BOOLEAN DEFAULT FALSE,

    -- Business Rules
    coverage_rules JSONB, -- Complex coverage rules and conditions
    exclusion_rules JSONB, -- What's not covered

    -- Validity Period
    effective_date DATE NOT NULL,
    expiry_date DATE,

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

    -- Timestamps
    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)
);

-- Patient insurance rights (used across OPD, IPD, ER financial systems)
CREATE TABLE fin_patient_insurance (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

    -- Patient Reference
    patient_id UUID NOT NULL REFERENCES patients(id) ON DELETE CASCADE,
    insurance_type_id UUID NOT NULL REFERENCES fin_insurance_types(id),

    -- Insurance Details
    policy_number VARCHAR(50),
    member_id VARCHAR(50),
    card_number VARCHAR(30),
    group_number VARCHAR(30),

    -- Validity Period
    effective_date DATE NOT NULL,
    expiry_date DATE,

    -- Coverage Information
    coverage_limit DECIMAL(15,2),
    used_amount DECIMAL(15,2) DEFAULT 0.00,
    remaining_amount DECIMAL(15,2) GENERATED ALWAYS AS (
        CASE
            WHEN coverage_limit IS NOT NULL THEN coverage_limit - used_amount
            ELSE NULL
        END
    ) STORED,

    -- Employer Information (for group insurance)
    employer_name VARCHAR(200),
    employer_code VARCHAR(20),

    -- Government Approval (for CSMBS - ข้าราชการ)
    approve_code VARCHAR(50),
    approve_code_expires_at TIMESTAMP WITH TIME ZONE,
    approved_amount DECIMAL(15,2),

    -- Priority and Settings
    is_primary BOOLEAN DEFAULT TRUE,
    priority_order INTEGER DEFAULT 1 CHECK (priority_order > 0),
    copayment_override DECIMAL(5,4) CHECK (copayment_override >= 0 AND copayment_override <= 1),

    -- Verification Status
    last_verified_at TIMESTAMP WITH TIME ZONE,
    verification_status VARCHAR(20) DEFAULT 'pending' CHECK (verification_status IN (
        'pending', 'verified', 'expired', 'suspended', 'invalid', 'error'
    )),
    verification_response JSONB,

    -- Additional Information
    notes TEXT,
    special_conditions JSONB,

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

    -- Timestamps
    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)
);

-- Real-time insurance verification logs (used by all financial modules)
CREATE TABLE fin_insurance_verifications (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

    -- Insurance Reference
    patient_insurance_id UUID NOT NULL REFERENCES fin_patient_insurance(id),
    visit_id UUID REFERENCES medical_visits(id),

    -- Verification Details
    verification_type VARCHAR(30) NOT NULL CHECK (verification_type IN (
        'eligibility', 'preauth', 'claim_submission', 'payment_status'
    )),
    verification_purpose VARCHAR(100), -- why this verification was requested

    -- API Call Information
    api_endpoint VARCHAR(500) NOT NULL,
    request_method VARCHAR(10) DEFAULT 'POST',
    request_payload JSONB NOT NULL,
    response_payload JSONB,

    -- Response Analysis
    verification_status VARCHAR(20) NOT NULL CHECK (verification_status IN (
        'success', 'failed', 'timeout', 'invalid_request', 'system_error'
    )),
    response_time_ms INTEGER,
    response_code VARCHAR(20),
    error_message TEXT,

    -- Verification Results
    is_eligible BOOLEAN,
    eligible_amount DECIMAL(15,2),
    copayment_required DECIMAL(15,2),
    preauth_required BOOLEAN DEFAULT FALSE,
    preauth_number VARCHAR(50),

    -- Validity Information
    verification_valid_until TIMESTAMP WITH TIME ZONE,
    expires_at TIMESTAMP WITH TIME ZONE,

    -- User Context
    verified_by_user_id UUID REFERENCES users(id),
    department_id UUID REFERENCES departments(id),

    -- Integration Context
    external_reference_id VARCHAR(100),
    correlation_id UUID,

    -- Timestamps
    verified_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

Field Descriptions for Insurance Tables

Table: fin_insurance_types

Field	Type	Constraints	คำอธิบาย
id	UUID	PRIMARY KEY	รหัสประเภทสิทธิ์ (Auto generate UUID)
insurance_code	VARCHAR(10)	UNIQUE NOT NULL	รหัสสิทธิ์ตาม 43 แฟ้ม (01-43)
insurance_name_thai	VARCHAR(200)	NOT NULL	ชื่อสิทธิ์ภาษาไทย
insurance_name_eng	VARCHAR(200)	NULL	ชื่อสิทธิ์ภาษาอังกฤษ
insurance_category	VARCHAR(50)	NOT NULL	หมวดหมู่สิทธิ์ (UC, SSO, CSMBS, Private, Cash)
payer_organization	VARCHAR(200)	NOT NULL	หน่วยงานผู้จ่าย (สปสช., สปท., กรมบัญชีกลาง)
payer_code	VARCHAR(20)	NULL	รหัสหน่วยงานผู้จ่าย
requires_approval	BOOLEAN	DEFAULT FALSE	ต้องการ Approve Code หรือไม่
requires_preauth	BOOLEAN	DEFAULT FALSE	ต้องการอนุมัติล่วงหน้าหรือไม่
copayment_rate	DECIMAL(5,4)	DEFAULT 0.0000	อัตราค่าร่วมจ่าย (0.0000-1.0000)
coverage_limit	DECIMAL(15,2)	NULL	วงเงินคุ้มครอง
annual_limit	DECIMAL(15,2)	NULL	วงเงินต่อปี
claim_format	VARCHAR(20)	DEFAULT 'e-claim'	รูปแบบการเบิกจ่าย
api_endpoint	VARCHAR(500)	NULL	URL สำหรับตรวจสอบสิทธิ์
supports_realtime_verification	BOOLEAN	DEFAULT FALSE	รองรับการตรวจสอบแบบ Real-time
coverage_rules	JSONB	NULL	กฎเกณฑ์การคุ้มครอง
exclusion_rules	JSONB	NULL	รายการที่ไม่คุ้มครอง
effective_date	DATE	NOT NULL	วันที่เริ่มใช้งาน
expiry_date	DATE	NULL	วันที่หมดอายุ
is_active	BOOLEAN	DEFAULT TRUE	สถานะการใช้งาน
is_deleted	BOOLEAN	DEFAULT FALSE	สถานะการลบ
created_at	TIMESTAMP WITH TIME ZONE	DEFAULT CURRENT_TIMESTAMP	วันเวลาที่สร้าง
updated_at	TIMESTAMP WITH TIME ZONE	DEFAULT CURRENT_TIMESTAMP	วันเวลาที่แก้ไขล่าสุด
created_by	UUID	FK users(id)	ผู้สร้างข้อมูล
updated_by	UUID	FK users(id)	ผู้แก้ไขข้อมูลล่าสุด

Table: fin_patient_insurance

Field	Type	Constraints	คำอธิบาย
id	UUID	PRIMARY KEY	รหัสสิทธิ์ผู้ป่วย (Auto generate UUID)
patient_id	UUID	NOT NULL, FK patients(id)	รหัสผู้ป่วย
insurance_type_id	UUID	NOT NULL, FK fin_insurance_types(id)	รหัสประเภทสิทธิ์
policy_number	VARCHAR(50)	NULL	หมายเลขกรมธรรม์
member_id	VARCHAR(50)	NULL	รหัสสมาชิก
card_number	VARCHAR(30)	NULL	หมายเลขบัตร
group_number	VARCHAR(30)	NULL	หมายเลขกลุ่ม
effective_date	DATE	NOT NULL	วันที่เริ่มใช้สิทธิ์
expiry_date	DATE	NULL	วันที่หมดอายุสิทธิ์
coverage_limit	DECIMAL(15,2)	NULL	วงเงินคุ้มครอง
used_amount	DECIMAL(15,2)	DEFAULT 0.00	จำนวนเงินที่ใช้ไปแล้ว
remaining_amount	DECIMAL(15,2)	GENERATED ALWAYS AS	จำนวนเงินคงเหลือ (คำนวณอัตโนมัติ)
employer_name	VARCHAR(200)	NULL	ชื่อนายจ้าง
employer_code	VARCHAR(20)	NULL	รหัสนายจ้าง
approve_code	VARCHAR(50)	NULL	รหัสอนุมัติ (สำหรับข้าราชการ)
approve_code_expires_at	TIMESTAMP WITH TIME ZONE	NULL	วันหมดอายุรหัสอนุมัติ
approved_amount	DECIMAL(15,2)	NULL	จำนวนเงินที่อนุมัติ
is_primary	BOOLEAN	DEFAULT TRUE	เป็นสิทธิ์หลักหรือไม่
priority_order	INTEGER	DEFAULT 1, CHECK > 0	ลำดับความสำคัญ
copayment_override	DECIMAL(5,4)	CHECK 0-1	อัตราค่าร่วมจ่ายพิเศษ
last_verified_at	TIMESTAMP WITH TIME ZONE	NULL	วันเวลาที่ตรวจสอบล่าสุด
verification_status	VARCHAR(20)	DEFAULT 'pending'	สถานะการตรวจสอบ
verification_response	JSONB	NULL	ผลตอบกลับการตรวจสอบ
notes	TEXT	NULL	หมายเหตุ
special_conditions	JSONB	NULL	เงื่อนไขพิเศษ
is_active	BOOLEAN	DEFAULT TRUE	สถานะการใช้งาน
is_deleted	BOOLEAN	DEFAULT FALSE	สถานะการลบ
created_at	TIMESTAMP WITH TIME ZONE	DEFAULT CURRENT_TIMESTAMP	วันเวลาที่สร้าง
updated_at	TIMESTAMP WITH TIME ZONE	DEFAULT CURRENT_TIMESTAMP	วันเวลาที่แก้ไขล่าสุด
created_by	UUID	FK users(id)	ผู้สร้างข้อมูล
updated_by	UUID	FK users(id)	ผู้แก้ไขข้อมูลล่าสุด

Table: fin_insurance_verifications

Field	Type	Constraints	คำอธิบาย
id	UUID	PRIMARY KEY	รหัสการตรวจสอบ (Auto generate UUID)
patient_insurance_id	UUID	NOT NULL, FK fin_patient_insurance(id)	รหัสสิทธิ์ผู้ป่วย
visit_id	UUID	FK medical_visits(id)	รหัสการมารับบริการ
verification_type	VARCHAR(30)	NOT NULL	ประเภทการตรวจสอบ
verification_purpose	VARCHAR(100)	NULL	วัตถุประสงค์การตรวจสอบ
api_endpoint	VARCHAR(500)	NOT NULL	ที่อยู่ API ที่ใช้ตรวจสอบ
request_method	VARCHAR(10)	DEFAULT 'POST'	วิธีการเรียก API
request_payload	JSONB	NOT NULL	ข้อมูลที่ส่งไป
response_payload	JSONB	NULL	ข้อมูลที่ตอบกลับ
verification_status	VARCHAR(20)	NOT NULL	สถานะการตรวจสอบ
response_time_ms	INTEGER	NULL	เวลาตอบสนอง (มิลลิวินาที)
response_code	VARCHAR(20)	NULL	รหัสตอบกลับ
error_message	TEXT	NULL	ข้อความแสดงข้อผิดพลาด
is_eligible	BOOLEAN	NULL	มีสิทธิ์หรือไม่
eligible_amount	DECIMAL(15,2)	NULL	จำนวนเงินที่มีสิทธิ์
copayment_required	DECIMAL(15,2)	NULL	ค่าร่วมจ่ายที่ต้องชำระ
preauth_required	BOOLEAN	DEFAULT FALSE	ต้องการอนุมัติล่วงหน้าหรือไม่
preauth_number	VARCHAR(50)	NULL	หมายเลขอนุมัติล่วงหน้า
verification_valid_until	TIMESTAMP WITH TIME ZONE	NULL	การตรวจสอบใช้ได้ถึงเมื่อไร
expires_at	TIMESTAMP WITH TIME ZONE	NULL	วันหมดอายุ
verified_by_user_id	UUID	FK users(id)	ผู้ทำการตรวจสอบ
department_id	UUID	FK departments(id)	แผนกที่ตรวจสอบ
external_reference_id	VARCHAR(100)	NULL	รหัสอ้างอิงจากระบบภายนอก
correlation_id	UUID	NULL	รหัสเชื่อมโยง
verified_at	TIMESTAMP WITH TIME ZONE	DEFAULT CURRENT_TIMESTAMP	วันเวลาที่ตรวจสอบ
created_at	TIMESTAMP WITH TIME ZONE	DEFAULT CURRENT_TIMESTAMP	วันเวลาที่สร้างข้อมูล

SECTION 7: INTEGRATION & COMMUNICATION

Multi-Channel Communication System

-- Notification management for all channels
CREATE TABLE notifications (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

    -- Recipient Information
    patient_id UUID REFERENCES patients(id),
    user_id UUID REFERENCES users(id),

    -- Notification Details
    notification_type VARCHAR(50) NOT NULL, -- appointment_reminder, lab_result, medication_alert
    title VARCHAR(200) NOT NULL,
    message TEXT NOT NULL,

    -- Channel Information
    channels JSONB NOT NULL, -- ['sms', 'line', 'email', 'push_notification']
    priority VARCHAR(20) DEFAULT 'normal' CHECK (priority IN ('low', 'normal', 'high', 'urgent')),

    -- Scheduling
    scheduled_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    send_after TIMESTAMP WITH TIME ZONE,
    expires_at TIMESTAMP WITH TIME ZONE,

    -- Delivery Status
    status VARCHAR(20) DEFAULT 'pending' CHECK (status IN ('pending', 'sent', 'delivered', 'failed', 'expired')),

    -- Channel-specific tracking
    sms_sent BOOLEAN DEFAULT FALSE,
    sms_delivered BOOLEAN DEFAULT FALSE,
    email_sent BOOLEAN DEFAULT FALSE,
    email_opened BOOLEAN DEFAULT FALSE,
    line_sent BOOLEAN DEFAULT FALSE,
    line_delivered BOOLEAN DEFAULT FALSE,

    -- Response Tracking
    response_required BOOLEAN DEFAULT FALSE,
    response_received BOOLEAN DEFAULT FALSE,
    response_data JSONB,

    -- Status and Audit
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    created_by UUID REFERENCES users(id)
);

-- External system integrations
CREATE TABLE external_integrations (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

    -- System Information
    system_name VARCHAR(100) NOT NULL,
    system_type VARCHAR(50) NOT NULL, -- NHSO, SSO, LIS, RIS, PACS, EMR

    -- Connection Details
    endpoint_url VARCHAR(500),
    api_version VARCHAR(20),
    authentication_type VARCHAR(50), -- api_key, oauth, basic_auth

    -- Configuration
    configuration JSONB NOT NULL,
    mapping_rules JSONB, -- data mapping configurations

    -- Status and Monitoring
    is_active BOOLEAN DEFAULT TRUE,
    last_sync_at TIMESTAMP WITH TIME ZONE,
    sync_frequency INTEGER, -- minutes

    -- Error Handling
    max_retry_attempts INTEGER DEFAULT 3,
    retry_interval INTEGER DEFAULT 300, -- seconds

    -- Performance Metrics
    total_requests INTEGER DEFAULT 0,
    successful_requests INTEGER DEFAULT 0,
    failed_requests INTEGER DEFAULT 0,
    average_response_time DECIMAL(8,2), -- milliseconds

    -- Status and Audit
    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)
);

SECTION 6: AUDIT & COMPLIANCE

Comprehensive Audit System

-- Comprehensive audit trail for healthcare compliance
CREATE TABLE audit_logs (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

    -- Operation Information
    operation VARCHAR(20) NOT NULL CHECK (operation IN ('CREATE', 'UPDATE', 'DELETE', 'SELECT', 'LOGIN', 'LOGOUT')),
    table_name VARCHAR(100) NOT NULL,
    record_id VARCHAR(100),

    -- User and Session Information
    user_id UUID REFERENCES users(id),
    session_id VARCHAR(255),
    impersonated_by UUID REFERENCES users(id),

    -- Data Changes
    old_values JSONB,
    new_values JSONB,
    changed_fields JSONB,

    -- Context Information
    module VARCHAR(50),
    function_name VARCHAR(100),
    request_url VARCHAR(500),
    http_method VARCHAR(10),

    -- Client Information
    ip_address INET,
    user_agent TEXT,
    device_info JSONB,

    -- Geographic Information
    country_code VARCHAR(2),
    city VARCHAR(100),

    -- Business Context
    business_reason VARCHAR(200),
    workflow_step VARCHAR(50),
    approval_required BOOLEAN DEFAULT FALSE,
    approved_by UUID REFERENCES users(id),

    -- Security and Risk
    risk_level VARCHAR(20) DEFAULT 'low' CHECK (risk_level IN ('low', 'medium', 'high', 'critical')),
    security_tags JSONB,

    -- Compliance Information
    compliance_category VARCHAR(50), -- PDPA, ISO27001, Medical_Records_Act
    retention_period INTEGER DEFAULT 2555, -- days (7 years for medical records)
    legal_hold BOOLEAN DEFAULT FALSE,

    -- Performance Information
    execution_time_ms INTEGER,
    query_count INTEGER,

    -- Status and Quality
    status VARCHAR(20) DEFAULT 'completed' CHECK (status IN ('completed', 'failed', 'partial', 'rolled_back')),
    error_message TEXT,

    -- Additional Information
    tags JSONB,
    notes TEXT,
    correlation_id UUID,

    -- Timestamps
    event_timestamp TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,

    -- Search optimization
    search_vector tsvector GENERATED ALWAYS AS (
        to_tsvector('simple', 
            table_name || ' ' || COALESCE(record_id, '') || ' ' ||
            COALESCE(module, '') || ' ' || COALESCE(function_name, '') || ' ' ||
            COALESCE(business_reason, '') || ' ' || operation
        )
    ) STORED
);

PERFORMANCE OPTIMIZATION STRATEGY

Indexing Strategy

-- Foundation table indexes
CREATE INDEX idx_users_employee_id ON users(employee_id) WHERE is_active = TRUE;
CREATE INDEX idx_users_username ON users(username) WHERE is_active = TRUE;
CREATE INDEX idx_users_department ON users(department_id, is_active);
CREATE INDEX idx_users_search_vector ON users USING gin(search_vector);

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_search_vector ON patients USING gin(search_vector);

CREATE INDEX idx_medical_visits_patient ON medical_visits(patient_id, visit_date DESC);
CREATE INDEX idx_medical_visits_department ON medical_visits(department_id, visit_date);
CREATE INDEX idx_medical_visits_status ON medical_visits(visit_status, visit_date);

-- Clinical system indexes
CREATE INDEX idx_appointments_patient_date ON appointments(patient_id, appointment_date);
CREATE INDEX idx_appointments_doctor_date ON appointments(doctor_id, appointment_date);
CREATE INDEX idx_appointments_department_date ON appointments(department_id, appointment_date);

CREATE INDEX idx_queues_department_date ON queues(department_id, queue_date, created_at);
CREATE INDEX idx_queues_status_priority ON queues(queue_status, queue_priority DESC, created_at);

CREATE INDEX idx_medical_orders_visit ON medical_orders(visit_id, order_date_time DESC);
CREATE INDEX idx_medical_orders_type_status ON medical_orders(order_type, order_status);
CREATE INDEX idx_medical_orders_department ON medical_orders(executing_department_id, order_date_time DESC);

-- Audit and compliance indexes
CREATE INDEX idx_audit_logs_table_time ON audit_logs(table_name, event_timestamp DESC);
CREATE INDEX idx_audit_logs_user_time ON audit_logs(user_id, event_timestamp DESC);
CREATE INDEX idx_audit_logs_search ON audit_logs USING gin(search_vector);

Partitioning Strategy

-- Partition audit logs by month for performance
CREATE TABLE audit_logs_template (LIKE audit_logs INCLUDING ALL);

-- Monthly partitions for audit logs
CREATE TABLE audit_logs_2025_01 PARTITION OF audit_logs
    FOR VALUES FROM ('2025-01-01') TO ('2025-02-01');

-- Quarterly partitions for large operational tables
CREATE TABLE medical_orders_template (LIKE medical_orders INCLUDING ALL);

CREATE TABLE medical_orders_2025_q1 PARTITION OF medical_orders
    FOR VALUES FROM ('2025-01-01') TO ('2025-04-01');

HEALTHCARE COMPLIANCE FEATURES

Data Retention & Privacy

-- Data retention policies for healthcare compliance
CREATE TABLE data_retention_policies (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

    -- Policy Information
    policy_name VARCHAR(100) NOT NULL,
    table_name VARCHAR(100) NOT NULL,

    -- Retention Rules
    retention_period_days INTEGER NOT NULL, -- 2555 days = 7 years for medical records
    retention_basis VARCHAR(50) NOT NULL, -- legal_requirement, business_need

    -- Legal Requirements
    legal_framework VARCHAR(100), -- Medical Records Act, PDPA, ISO 27001
    regulatory_requirement TEXT,

    -- Deletion Rules
    deletion_method VARCHAR(30) DEFAULT 'soft_delete' CHECK (deletion_method IN ('soft_delete', 'hard_delete', 'anonymize')),

    -- Status
    is_active BOOLEAN DEFAULT TRUE,

    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- Patient consent management for PDPA compliance
CREATE TABLE patient_consents (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

    -- Patient Information
    patient_id UUID NOT NULL REFERENCES patients(id),

    -- Consent Details
    consent_type VARCHAR(50) NOT NULL, -- data_processing, marketing, research
    consent_purpose TEXT NOT NULL,

    -- Consent Status
    consent_given BOOLEAN NOT NULL,
    consent_date TIMESTAMP WITH TIME ZONE NOT NULL,
    consent_method VARCHAR(30), -- written, digital, verbal

    -- Withdrawal
    withdrawn BOOLEAN DEFAULT FALSE,
    withdrawal_date TIMESTAMP WITH TIME ZONE,
    withdrawal_reason TEXT,

    -- Legal Basis
    legal_basis VARCHAR(50), -- consent, legitimate_interest, legal_obligation

    -- Audit Trail
    consent_document_path VARCHAR(500),
    witnessed_by UUID REFERENCES users(id),

    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

SYSTEM CONFIGURATION

Master Configuration System

-- Unified system configuration for all modules
CREATE TABLE system_configurations (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

    -- Configuration Identification
    config_key VARCHAR(100) UNIQUE NOT NULL, -- hospital.name, session.timeout
    config_name_thai VARCHAR(200) NOT NULL,
    config_name_eng VARCHAR(200),
    description TEXT,

    -- Configuration Category
    category VARCHAR(50) NOT NULL, -- hospital, security, integration, ui, clinical
    subcategory VARCHAR(50),
    module VARCHAR(50), -- EMR, CPOE, Queue, Lab, etc.

    -- Configuration Value
    config_value TEXT,
    data_type VARCHAR(20) NOT NULL CHECK (data_type IN (
        'string', 'integer', 'boolean', 'json', 'decimal', 'date', 'time', 'file'
    )),

    -- Value Constraints
    default_value TEXT,
    min_value DECIMAL,
    max_value DECIMAL,
    allowed_values JSONB,
    validation_regex VARCHAR(500),

    -- Configuration Metadata
    is_sensitive BOOLEAN DEFAULT FALSE,
    is_system_config BOOLEAN DEFAULT FALSE,
    requires_restart BOOLEAN DEFAULT FALSE,

    -- Access Control
    edit_permission VARCHAR(100),
    view_permission VARCHAR(100),

    -- UI Information
    display_order INTEGER DEFAULT 0,
    ui_component VARCHAR(30), -- input, select, checkbox, file_upload
    ui_options JSONB,
    help_text_thai TEXT,
    help_text_eng TEXT,

    -- Environment Specific
    environment VARCHAR(20) DEFAULT 'all' CHECK (environment IN ('all', 'development', 'staging', 'production')),

    -- Version Control
    version INTEGER DEFAULT 1,
    previous_value TEXT,
    change_reason TEXT,

    -- Status and Validation
    is_active BOOLEAN DEFAULT TRUE,
    is_valid BOOLEAN DEFAULT TRUE,
    validation_error TEXT,
    last_validated_at TIMESTAMP WITH TIME ZONE,

    -- Audit Trail
    created_by UUID NOT NULL REFERENCES users(id),
    updated_by UUID REFERENCES users(id),
    approved_by UUID REFERENCES users(id),
    approval_notes TEXT,

    -- Timestamps
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,

    -- Search optimization
    search_vector tsvector GENERATED ALWAYS AS (
        to_tsvector('thai', 
            config_name_thai || ' ' || COALESCE(config_name_eng, '') || ' ' ||
            config_key || ' ' || COALESCE(description, '')
        )
    ) STORED
);

INTEGRATION ARCHITECTURE

Cross-Module Data Exchange

-- Integration events for module communication
CREATE TABLE integration_events (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

    -- Event Information
    event_type VARCHAR(50) NOT NULL, -- patient_created, visit_completed, order_placed
    source_module VARCHAR(50) NOT NULL, -- EMR, Queue, CPOE, Lab
    target_modules JSONB NOT NULL, -- ['Billing', 'Pharmacy', 'Lab']

    -- Event Data
    entity_type VARCHAR(50) NOT NULL, -- patient, visit, order, appointment
    entity_id UUID NOT NULL,
    event_data JSONB NOT NULL,

    -- Processing Status
    status VARCHAR(20) DEFAULT 'pending' CHECK (status IN ('pending', 'processing', 'completed', 'failed')),
    processed_modules JSONB DEFAULT '[]',
    failed_modules JSONB DEFAULT '[]',

    -- Retry Logic
    retry_count INTEGER DEFAULT 0,
    max_retries INTEGER DEFAULT 3,
    next_retry_at TIMESTAMP WITH TIME ZONE,

    -- Error Information
    error_message TEXT,
    error_details JSONB,

    -- Timestamps
    event_timestamp TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    processed_at TIMESTAMP WITH TIME ZONE,
    completed_at TIMESTAMP WITH TIME ZONE,

    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- API call logs for external integrations
CREATE TABLE api_call_logs (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

    -- Request Information
    system_name VARCHAR(100) NOT NULL, -- NHSO, SSO, External_Lab
    endpoint VARCHAR(500) NOT NULL,
    method VARCHAR(10) NOT NULL, -- GET, POST, PUT, DELETE

    -- Request Data
    request_headers JSONB,
    request_body TEXT,

    -- Response Data
    response_status INTEGER,
    response_headers JSONB,
    response_body TEXT,

    -- Timing Information
    request_timestamp TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    response_timestamp TIMESTAMP WITH TIME ZONE,
    response_time_ms INTEGER,

    -- Context
    user_id UUID REFERENCES users(id),
    session_id VARCHAR(255),
    correlation_id UUID,

    -- Error Handling
    is_successful BOOLEAN DEFAULT FALSE,
    error_message TEXT,
    retry_attempt INTEGER DEFAULT 0,

    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

STANDARDIZATION REQUIREMENTS

Naming Conventions

Component	Convention	Example
Tables	snake_case	`medical_visits`, `patient_consents`
Columns	snake_case	`patient_id`, `created_at`
Primary Keys	`id` (UUID)	`id UUID PRIMARY KEY`
Foreign Keys	`{table}_id`	`patient_id`, `user_id`
Timestamps	`{action}_at`	`created_at`, `updated_at`
Boolean Flags	`is_{condition}`	`is_active`, `is_deleted`
Status Fields	`{entity}_status`	`visit_status`, `queue_status`

Data Types Standardization

-- Standard field patterns across all tables
-- Primary Keys: UUID DEFAULT gen_random_uuid()
-- Timestamps: TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
-- Status flags: BOOLEAN DEFAULT TRUE/FALSE
-- Text fields: TEXT (unlimited) or VARCHAR(n) with specific limits
-- JSON data: JSONB (for better performance and indexing)
-- Money amounts: DECIMAL(10,2)
-- IP addresses: INET
-- Search optimization: tsvector GENERATED ALWAYS AS (...) STORED

Common Patterns

-- Standard audit fields for all transactional tables
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),

-- Standard soft delete pattern
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,

-- Standard search pattern
search_vector tsvector GENERATED ALWAYS AS (...) STORED

IMPLEMENTATION ROADMAP

Phase 1: Foundation (Months 1-2)

Core Infrastructure
System Administration Module
User Management & RBAC
Digital Signature System
Basic Audit Trail
Patient Management
EMR Core System
Patient Registration
Demographics Management
Insurance Integration

Phase 2: Clinical Core (Months 3-4)

Appointment System
Scheduling & Queue Management
Multi-channel Booking
Real-time Queue Display
Clinical Documentation
SOAP Notes
Medical Orders (CPOE)
Basic AI Assist Integration

Phase 3: Diagnostic Systems (Months 5-6)

Laboratory Systems
Order Management
Result Reporting
Critical Value Alerts
Radiology Systems
Image Order Management
DICOM Integration
Reporting Workflow

Phase 4: Advanced Features (Months 7-8)

Inpatient Systems
Admission Management
Nursing Documentation
Medication Administration
Financial Systems
Billing Integration
DRG Processing
Insurance Claims

SECURITY & COMPLIANCE CHECKLIST

Healthcare Compliance

✅ Medical Records Act: 7-year retention period
✅ PDPA: Patient consent management and data protection
✅ ISO 27001: Information security management
✅ Digital Signature Act: Legal digital signature support

Security Features

✅ Encryption: AES-256 for sensitive data
✅ Authentication: Multi-factor authentication support
✅ Authorization: Role-based access control
✅ Audit Trail: Comprehensive logging
✅ Data Integrity: Digital signatures for documents

Performance Requirements

✅ Response Time: <500ms for patient searches
✅ Concurrent Users: 100+ simultaneous users
✅ Real-time Updates: WebSocket support for queues
✅ Scalability: Partitioning strategy for growth

CONCLUSION

This Master Database Schema provides a comprehensive, integrated foundation for the entire MediTech Hospital Information System. The design prioritizes:

Healthcare Compliance: Full adherence to Thai healthcare regulations and international standards
Integration Readiness: Seamless data flow between all 32 modules
Performance Optimization: Scalable architecture supporting 100+ concurrent users
Security First: Comprehensive audit trails and role-based access control
Development Efficiency: Standardized patterns enabling rapid module development

The schema serves as the single source of truth for database design across all MediTech modules, ensuring consistency, maintainability, and regulatory compliance throughout the 8-month development timeline.

Key Benefits:

Unified Data Model: Eliminates data silos between modules
Regulatory Compliance: Built-in PDPA, ISO 27001, and medical record standards
Performance Optimized: Strategic indexing and partitioning for scale
API-Ready: Designed for modern REST API and real-time WebSocket integration
Future-Proof: Extensible architecture for additional modules and requirements

This master schema enables the MediTech development team to build a world-class Hospital Information System that meets the complex requirements of modern healthcare delivery while maintaining the highest standards of data security, patient privacy, and clinical excellence.

Master Database Schema - MediTech Hospital Information System

Executive Summary

Key Design Principles:

Table of Contents

SECTION 1: FOUNDATION TABLES (Shared across all modules)

SECTION 2: AUTHENTICATION & SECURITY

SECTION 3: CLINICAL CORE SYSTEMS

SECTION 4: DIAGNOSTIC SYSTEMS

SECTION 5: PATIENT CARE SYSTEMS

SECTION 6: FINANCIAL SYSTEMS

SECTION 7: INTEGRATION & COMMUNICATION

SECTION 1: FOUNDATION TABLES

Core Shared Tables - The Foundation of All Modules

1.1 Master User Management

1.2 Patient Demographics (Master Patient Index)

1.3 Hospital Organization Structure

1.4 Visit Management (Core Clinical Encounters)

SECTION 2: AUTHENTICATION & SECURITY

Role-Based Access Control System

Digital Signature System

SECTION 3: CLINICAL CORE SYSTEMS

Appointment & Queue Management Integration

Medical Orders (CPOE) System

SECTION 4: DIAGNOSTIC SYSTEMS

Laboratory Systems Integration

SECTION 6: FINANCIAL SYSTEMS

Insurance & Rights Management (ใช้ร่วมกันทุกโมดูลการเงิน)

Field Descriptions for Insurance Tables

SECTION 7: INTEGRATION & COMMUNICATION

Multi-Channel Communication System

SECTION 6: AUDIT & COMPLIANCE

Comprehensive Audit System

PERFORMANCE OPTIMIZATION STRATEGY

Indexing Strategy

Partitioning Strategy

HEALTHCARE COMPLIANCE FEATURES

Data Retention & Privacy

SYSTEM CONFIGURATION

Master Configuration System

INTEGRATION ARCHITECTURE

Cross-Module Data Exchange

STANDARDIZATION REQUIREMENTS

Naming Conventions

Data Types Standardization

Common Patterns

IMPLEMENTATION ROADMAP

Phase 1: Foundation (Months 1-2)

Phase 2: Clinical Core (Months 3-4)

Phase 3: Diagnostic Systems (Months 5-6)

Phase 4: Advanced Features (Months 7-8)

SECURITY & COMPLIANCE CHECKLIST

Healthcare Compliance

Security Features

Performance Requirements

CONCLUSION

Key Benefits: