Data Dictionary & ER Diagram — สร้างจากโครงฐานข้อมูลจริง (information_schema)
| คอลัมน์ | ชนิด | Null | Key / อ้างอิง |
|---|---|---|---|
| id | text | — | PK |
| hn | text | ✓ | — |
| checkupId | text | — | — |
| nationalId | text | ✓ | — |
| titleName | text | ✓ | — |
| firstName | text | — | — |
| lastName | text | — | — |
* = NOT NULL · หน้านี้อ่านโครงสร้างสด ๆ จากฐานข้อมูล จึงตรงกับสคีมาปัจจุบันเสมอ
| gender | enum | — | — |
| dateOfBirth | date | ✓ | — |
| phone | text | ✓ | — |
| text | ✓ | — |
| address | text | ✓ | — |
| source | enum | — | — |
| hisSyncedAt | timestamp | ✓ | — |
| registeredById | text | ✓ | users.id |
| createdAt | timestamp | — | — |
| updatedAt | timestamp | — | — |
| nationalIdHash | text | ✓ | — |
| anonymizedAt | timestamp | ✓ | — |
| bloodType | text | ✓ | — |
| insuranceType | text | ✓ | — |
| allergies | text | ✓ | — |
| location | text | ✓ | — |
| คอลัมน์ | ชนิด | Null | Key / อ้างอิง |
|---|---|---|---|
| id | text | — | PK |
| employeeId | text | ✓ | — |
| text | — | — | |
| passwordHash | text | ✓ | — |
| fullName | text | — | — |
| role | enum | — | — |
| licenseNo | text | ✓ | — |
| isActive | bool | — | — |
| createdAt | timestamp | — | — |
| updatedAt | timestamp | — | — |
| คอลัมน์ | ชนิด | Null | Key / อ้างอิง |
|---|---|---|---|
| day | text | — | PK |
| lastSeq | int | — | — |
| คอลัมน์ | ชนิด | Null | Key / อ้างอิง |
|---|---|---|---|
| id | text | — | PK |
| actorId | text | ✓ | users.id |
| actorRole | enum | ✓ | — |
| action | enum | — | — |
| entityType | text | — | — |
| entityId | text | ✓ | — |
| targetPatientId | text | ✓ | — |
| description | text | ✓ | — |
| metadata | json | ✓ | — |
| ipAddress | text | ✓ | — |
| userAgent | text | ✓ | — |
| createdAt | timestamp | — | — |
| คอลัมน์ | ชนิด | Null | Key / อ้างอิง |
|---|---|---|---|
| id | text | — | PK |
| code | text | — | — |
| name | text | — | — |
| description | text | ✓ | — |
| priceCents | int | — | — |
| isActive | bool | — | — |
| createdAt | timestamp | — | — |
| updatedAt | timestamp | — | — |
| คอลัมน์ | ชนิด | Null | Key / อ้างอิง |
|---|---|---|---|
| id | text | — | PK |
| packageId | text | — | checkup_packages.id |
| testCode | text | — | — |
| testName | text | — | — |
| modality | enum | — | — |
| testId | text | ✓ | lab_tests.id |
| คอลัมน์ | ชนิด | Null | Key / อ้างอิง |
|---|---|---|---|
| id | text | — | PK |
| code | text | — | — |
| name | text | — | — |
| taxId | text | ✓ | — |
| contactName | text | ✓ | — |
| contactPhone | text | ✓ | — |
| note | text | ✓ | — |
| isActive | bool | — | — |
| createdAt | timestamp | — | — |
| updatedAt | timestamp | — | — |
| คอลัมน์ | ชนิด | Null | Key / อ้างอิง |
|---|---|---|---|
| id | text | — | PK |
| code | text | — | — |
| name | text | — | — |
| description | text | ✓ | — |
| sortOrder | int | — | — |
| isActive | bool | — | — |
| createdAt | timestamp | — | — |
| updatedAt | timestamp | — | — |
| คอลัมน์ | ชนิด | Null | Key / อ้างอิง |
|---|---|---|---|
| id | text | — | PK |
| visitNo | text | — | — |
| patientId | text | — | patients.id |
| packageId | text | — | checkup_packages.id |
| status | enum | — | — |
| visitDate | timestamp | — | — |
| createdById | text | ✓ | users.id |
| reviewedById | text | ✓ | users.id |
| conclusion | text | ✓ | — |
| recommendations | text | ✓ | — |
| reviewedAt | timestamp | ✓ | — |
| createdAt | timestamp | — | — |
| updatedAt | timestamp | — | — |
| payorId | text | ✓ | payors.id |
| followUpDate | timestamp | ✓ | — |
| followUpNote | text | ✓ | — |
| physicalExam | text | ✓ | — |
| location | text | ✓ | — |
| chiefComplaint | text | ✓ | — |
| presentIllness | text | ✓ | — |
| pastIllness | text | ✓ | — |
| nurseExam | text | ✓ | — |
| alcohol | text | ✓ | — |
| smoking | text | ✓ | — |
| exercise | text | ✓ | — |
| surgeryHistory | text | ✓ | — |
| familyHistory | text | ✓ | — |
| คอลัมน์ | ชนิด | Null | Key / อ้างอิง |
|---|---|---|---|
| id | text | — | PK |
| patientId | text | — | patients.id |
| visitId | text | ✓ | checkup_visits.id |
| doctorId | text | ✓ | users.id |
| appointmentAt | timestamp | — | — |
| durationMin | int | — | — |
| type | text | ✓ | — |
| reason | text | ✓ | — |
| status | enum | — | — |
| createdById | text | ✓ | users.id |
| createdAt | timestamp | — | — |
| updatedAt | timestamp | — | — |
| location | text | ✓ | — |
| locationId | text | ✓ | locations.id |
| packageId | text | ✓ | checkup_packages.id |
| คอลัมน์ | ชนิด | Null | Key / อ้างอิง |
|---|---|---|---|
| id | text | — | PK |
| code | text | — | — |
| name | text | — | — |
| modality | enum | — | — |
| description | text | ✓ | — |
| priceCents | int | — | — |
| isActive | bool | — | — |
| createdAt | timestamp | — | — |
| updatedAt | timestamp | — | — |
| unit | text | ✓ | — |
| refRange | text | ✓ | — |
| คอลัมน์ | ชนิด | Null | Key / อ้างอิง |
|---|---|---|---|
| id | text | — | PK |
| visitId | text | — | checkup_visits.id |
| testCode | text | — | — |
| testName | text | — | — |
| modality | enum | — | — |
| status | enum | — | — |
| hisOrderId | text | ✓ | — |
| placedById | text | ✓ | users.id |
| orderedAt | timestamp | — | — |
| testId | text | ✓ | lab_tests.id |
| คอลัมน์ | ชนิด | Null | Key / อ้างอิง |
|---|---|---|---|
| id | text | — | PK |
| orderId | text | — | lab_orders.id |
| analyteCode | text | — | — |
| analyteName | text | — | — |
| value | text | — | — |
| numericValue | float | ✓ | — |
| unit | text | ✓ | — |
| refRange | text | ✓ | — |
| flag | enum | — | — |
| hisResultId | text | ✓ | — |
| resultedAt | timestamp | — | — |
| createdAt | timestamp | — | — |
| คอลัมน์ | ชนิด | Null | Key / อ้างอิง |
|---|---|---|---|
| id | text | — | PK |
| visitId | text | — | checkup_visits.id |
| patientId | text | — | patients.id |
| temperature | float | ✓ | — |
| systolic | int | ✓ | — |
| diastolic | int | ✓ | — |
| heartRate | int | ✓ | — |
| respiratoryRate | int | ✓ | — |
| o2sat | int | ✓ | — |
| dtx | int | ✓ | — |
| weightKg | float | ✓ | — |
| heightCm | float | ✓ | — |
| bmi | float | ✓ | — |
| note | text | ✓ | — |
| recordedById | text | ✓ | users.id |
| recordedAt | timestamp | — | — |
| createdAt | timestamp | — | — |
| updatedAt | timestamp | — | — |
| คอลัมน์ | ชนิด | Null | Key / อ้างอิง |
|---|---|---|---|
| id | text | — | PK |
| batchId | text | — | — |
| patientId | text | — | patients.id |
| hn | text | ✓ | — |
| resultDate | timestamp | ✓ | — |
| testCode | text | ✓ | — |
| testName | text | — | — |
| value | text | — | — |
| numericValue | float | ✓ | — |
| unit | text | ✓ | — |
| refRange | text | ✓ | — |
| flag | enum | — | — |
| sourceFile | text | ✓ | — |
| importedById | text | ✓ | users.id |
| createdAt | timestamp | — | — |
| คอลัมน์ | ชนิด | Null | Key / อ้างอิง |
|---|---|---|---|
| id | text | — | PK |
| code | text | — | — |
| name | text | — | — |
| description | text | ✓ | — |
| sortOrder | int | — | — |
| isActive | bool | — | — |
| createdAt | timestamp | — | — |
| updatedAt | timestamp | — | — |
| คอลัมน์ | ชนิด | Null | Key / อ้างอิง |
|---|---|---|---|
| id | text | — | PK |
| code | text | — | — |
| name | text | — | — |
| serviceTypeId | text | ✓ | physio_service_types.id |
| priceCents | int | — | — |
| description | text | ✓ | — |
| isActive | bool | — | — |
| createdAt | timestamp | — | — |
| updatedAt | timestamp | — | — |
| คอลัมน์ | ชนิด | Null | Key / อ้างอิง |
|---|---|---|---|
| id | text | — | PK |
| patientId | text | — | patients.id |
| serviceTypeId | text | ✓ | physio_service_types.id |
| therapistId | text | ✓ | users.id |
| status | enum | — | — |
| queuedAt | timestamp | — | — |
| diagnosis | text | ✓ | — |
| bodyChart | json | ✓ | — |
| progressionNote | text | ✓ | — |
| summary | text | ✓ | — |
| createdById | text | ✓ | users.id |
| createdAt | timestamp | — | — |
| updatedAt | timestamp | — | — |
| คอลัมน์ | ชนิด | Null | Key / อ้างอิง |
|---|---|---|---|
| id | text | — | PK |
| visitId | text | — | physio_visits.id |
| procedureId | text | ✓ | physio_procedures.id |
| code | text | ✓ | — |
| name | text | — | — |
| priceCents | int | — | — |
| createdAt | timestamp | — | — |
| คอลัมน์ | ชนิด | Null | Key / อ้างอิง |
|---|---|---|---|
| id | text | — | PK |
| code | text | — | — |
| name | text | — | — |
| unit | text | — | — |
| category | text | ✓ | — |
| quantity | int | — | — |
| minQuantity | int | — | — |
| isActive | bool | — | — |
| createdAt | timestamp | — | — |
| updatedAt | timestamp | — | — |
| คอลัมน์ | ชนิด | Null | Key / อ้างอิง |
|---|---|---|---|
| id | text | — | PK |
| itemId | text | — | physio_stock_items.id |
| type | enum | — | — |
| quantity | int | — | — |
| balance | int | — | — |
| note | text | ✓ | — |
| createdById | text | ✓ | users.id |
| createdAt | timestamp | — | — |
| คอลัมน์ | ชนิด | Null | Key / อ้างอิง |
|---|---|---|---|
| id | text | — | PK |
| code | text | — | — |
| name | text | — | — |
| category | text | ✓ | — |
| icd9 | text | ✓ | — |
| priceCents | int | — | — |
| description | text | ✓ | — |
| isActive | bool | — | — |
| createdAt | timestamp | — | — |
| updatedAt | timestamp | — | — |
| คอลัมน์ | ชนิด | Null | Key / อ้างอิง |
|---|---|---|---|
| id | text | — | PK |
| patientId | text | — | patients.id |
| dentistId | text | ✓ | users.id |
| status | enum | — | — |
| queuedAt | timestamp | — | — |
| chiefComplaint | text | ✓ | — |
| historyNote | text | ✓ | — |
| diagnosisIcd10 | text | ✓ | — |
| procedureIcd9 | text | ✓ | — |
| toothChart | json | ✓ | — |
| note | text | ✓ | — |
| createdById | text | ✓ | users.id |
| createdAt | timestamp | — | — |
| updatedAt | timestamp | — | — |
| คอลัมน์ | ชนิด | Null | Key / อ้างอิง |
|---|---|---|---|
| id | text | — | PK |
| visitId | text | — | dental_visits.id |
| treatmentId | text | ✓ | dental_treatments.id |
| code | text | ✓ | — |
| name | text | — | — |
| tooth | text | ✓ | — |
| priceCents | int | — | — |
| createdAt | timestamp | — | — |
| คอลัมน์ | ชนิด | Null | Key / อ้างอิง |
|---|---|---|---|
| id | text | — | PK |
| code | text | — | — |
| name | text | — | — |
| unit | text | — | — |
| category | text | ✓ | — |
| quantity | int | — | — |
| minQuantity | int | — | — |
| costCents | int | — | — |
| expiryDate | date | ✓ | — |
| isActive | bool | — | — |
| createdAt | timestamp | — | — |
| updatedAt | timestamp | — | — |
| คอลัมน์ | ชนิด | Null | Key / อ้างอิง |
|---|---|---|---|
| id | text | — | PK |
| itemId | text | — | dental_stock_items.id |
| type | enum | — | — |
| quantity | int | — | — |
| balance | int | — | — |
| note | text | ✓ | — |
| createdById | text | ✓ | users.id |
| createdAt | timestamp | — | — |
| คอลัมน์ | ชนิด | Null | Key / อ้างอิง |
|---|---|---|---|
| id | text | — | PK |
| checksum | text | — | — |
| finished_at | timestamptz | ✓ | — |
| migration_name | text | — | — |
| logs | text | ✓ | — |
| rolled_back_at | timestamptz | ✓ | — |
| started_at | timestamptz | — | — |
| applied_steps_count | int | — | — |
| คอลัมน์ | ชนิด | Null | Key / อ้างอิง |
|---|---|---|---|
| id | text | — | PK |
| testId | text | — | lab_tests.id |
| analyteCode | text | — | — |
| analyteName | text | — | — |
| unit | text | ✓ | — |
| refRange | text | ✓ | — |
| sortOrder | int | — | — |
| createdAt | timestamp | — | — |
| updatedAt | timestamp | — | — |