Detailed technical specifications for developers implementing the IVF Information System.
-- Patients
CREATE TABLE patients (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
patient_code VARCHAR(20) UNIQUE NOT NULL,
full_name VARCHAR(200) NOT NULL,
date_of_birth DATE NOT NULL,
gender VARCHAR(10) CHECK (gender IN ('Male', 'Female')),
identity_number VARCHAR(20),
phone VARCHAR(20),
address TEXT,
photo BYTEA,
fingerprint BYTEA,
patient_type VARCHAR(20) CHECK (patient_type IN ('Infertility', 'EggDonor', 'SpermDonor')),
created_at TIMESTAMPTZ DEFAULT NOW(),
updated_at TIMESTAMPTZ DEFAULT NOW()
);
-- Couples
CREATE TABLE couples (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
wife_id UUID REFERENCES patients(id) NOT NULL,
husband_id UUID REFERENCES patients(id) NOT NULL,
sperm_donor_id UUID REFERENCES patients(id),
marriage_date DATE,
infertility_years INT,
created_at TIMESTAMPTZ DEFAULT NOW()
);
-- Treatment Cycles
CREATE TABLE treatment_cycles (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
couple_id UUID REFERENCES couples(id) NOT NULL,
cycle_code VARCHAR(20) UNIQUE NOT NULL,
method VARCHAR(10) CHECK (method IN ('QHTN', 'IUI', 'ICSI', 'IVM')),
current_phase VARCHAR(30),
start_date DATE NOT NULL,
end_date DATE,
outcome VARCHAR(30),
notes TEXT,
created_at TIMESTAMPTZ DEFAULT NOW()
);
-- Queue Tickets
CREATE TABLE queue_tickets (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
ticket_number VARCHAR(20) NOT NULL,
queue_type VARCHAR(20) NOT NULL,
patient_id UUID REFERENCES patients(id) NOT NULL,
cycle_id UUID REFERENCES treatment_cycles(id),
department_code VARCHAR(20) NOT NULL,
status VARCHAR(20) DEFAULT 'Waiting',
issued_at TIMESTAMPTZ DEFAULT NOW(),
called_at TIMESTAMPTZ,
completed_at TIMESTAMPTZ,
called_by_user_id UUID
);
-- Ultrasound Records
CREATE TABLE ultrasounds (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
cycle_id UUID REFERENCES treatment_cycles(id) NOT NULL,
exam_date TIMESTAMPTZ NOT NULL,
ultrasound_type VARCHAR(30), -- PhụKhoa, NangNoãn, NMTC, Thai
left_ovary_count INT,
right_ovary_count INT,
endometrium_thickness DECIMAL(5,2),
left_follicles JSONB, -- [{size: 15, position: 1}, ...]
right_follicles JSONB,
findings TEXT,
doctor_id UUID,
created_at TIMESTAMPTZ DEFAULT NOW()
);
-- Embryos
CREATE TABLE embryos (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
cycle_id UUID REFERENCES treatment_cycles(id) NOT NULL,
embryo_number INT NOT NULL,
fertilization_date DATE,
grade VARCHAR(5),
day VARCHAR(5) CHECK (day IN ('D1', 'D2', 'D3', 'D4', 'D5', 'D6')),
status VARCHAR(20) CHECK (status IN ('Developing', 'Transferred', 'Frozen', 'Discarded')),
cryo_location_id UUID,
freeze_date DATE,
thaw_date DATE,
notes TEXT
);
-- Cryo Storage Locations
CREATE TABLE cryo_locations (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
tank VARCHAR(20),
canister VARCHAR(20),
cane VARCHAR(20),
goblet VARCHAR(20),
straw VARCHAR(20),
specimen_type VARCHAR(20), -- Embryo, Sperm, Oocyte
is_occupied BOOLEAN DEFAULT FALSE
);
-- Semen Analysis
CREATE TABLE semen_analyses (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
patient_id UUID REFERENCES patients(id) NOT NULL,
cycle_id UUID REFERENCES treatment_cycles(id),
analysis_date TIMESTAMPTZ NOT NULL,
volume DECIMAL(5,2),
concentration DECIMAL(10,2),
motility_a DECIMAL(5,2),
motility_b DECIMAL(5,2),
motility_c DECIMAL(5,2),
motility_d DECIMAL(5,2),
morphology DECIMAL(5,2),
analysis_type VARCHAR(20), -- PreWash, PostWash
notes TEXT
);
-- Sperm Bank Donors
CREATE TABLE sperm_donors (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
patient_id UUID REFERENCES patients(id) NOT NULL,
donor_code VARCHAR(20) UNIQUE NOT NULL,
status VARCHAR(20) CHECK (status IN ('Screening', 'Active', 'Inactive', 'Rejected')),
screening_date DATE,
hiv_retest_date DATE,
hiv_retest_result VARCHAR(20),
max_couples INT DEFAULT 2,
current_couples INT DEFAULT 0
);
-- Sperm Samples
CREATE TABLE sperm_samples (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
donor_id UUID REFERENCES sperm_donors(id) NOT NULL,
sample_number INT NOT NULL,
collection_date DATE NOT NULL,
cryo_location_id UUID REFERENCES cryo_locations(id),
status VARCHAR(20) CHECK (status IN ('Available', 'Reserved', 'Used', 'Discarded')),
used_by_couple_id UUID REFERENCES couples(id),
used_date DATE
);
-- Invoices
CREATE TABLE invoices (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
invoice_number VARCHAR(30) UNIQUE NOT NULL,
patient_id UUID REFERENCES patients(id) NOT NULL,
cycle_id UUID REFERENCES treatment_cycles(id),
total_amount DECIMAL(15,2) NOT NULL,
paid_amount DECIMAL(15,2) DEFAULT 0,
status VARCHAR(20) CHECK (status IN ('Pending', 'Partial', 'Paid', 'Refunded')),
created_at TIMESTAMPTZ DEFAULT NOW(),
paid_at TIMESTAMPTZ
);
-- Invoice Items
CREATE TABLE invoice_items (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
invoice_id UUID REFERENCES invoices(id) NOT NULL,
service_code VARCHAR(30) NOT NULL,
service_name VARCHAR(200) NOT NULL,
quantity INT DEFAULT 1,
unit_price DECIMAL(15,2) NOT NULL,
discount DECIMAL(15,2) DEFAULT 0,
total DECIMAL(15,2) NOT NULL
);
-- Prescriptions
CREATE TABLE prescriptions (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
patient_id UUID REFERENCES patients(id) NOT NULL,
cycle_id UUID REFERENCES treatment_cycles(id),
doctor_id UUID NOT NULL,
prescription_date DATE NOT NULL,
status VARCHAR(20) DEFAULT 'Pending',
dispensed_at TIMESTAMPTZ,
notes TEXT
);
-- Prescription Items
CREATE TABLE prescription_items (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
prescription_id UUID REFERENCES prescriptions(id) NOT NULL,
drug_code VARCHAR(30),
drug_name VARCHAR(200) NOT NULL,
dosage VARCHAR(100),
frequency VARCHAR(100),
duration VARCHAR(50),
quantity INT NOT NULL
);
-- Users (Staff)
CREATE TABLE users (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
username VARCHAR(50) UNIQUE NOT NULL,
password_hash VARCHAR(255) NOT NULL,
full_name VARCHAR(200) NOT NULL,
role VARCHAR(30) NOT NULL,
department VARCHAR(50),
is_active BOOLEAN DEFAULT TRUE,
created_at TIMESTAMPTZ DEFAULT NOW()
);