A401ERD
Tables (9)
audit_logs
- event_no — bigint — Primary key — NOT NULL
- employee_no — varchar(50) — Foreign key → users.employee_no
- event_type — varchar(100) — NOT NULL
- target_type — varchar(50) — NOT NULL
- target_key — varchar(100) — NOT NULL
- outcome — varchar(30) — NOT NULL
- details — jsonb — NOT NULL
- created_at — timestamptz — NOT NULL
auth_sessions
- session_key — uuid — Primary key — NOT NULL
- prescription_no — varchar(100) — Foreign key → prescriptions.prescription_no
- employee_no — varchar(50) — Foreign key → users.employee_no
- action_type — varchar(20) — NOT NULL
- rfid_verified — boolean — NOT NULL
- face_verified — boolean — NOT NULL
- assignee_matched — boolean — NOT NULL
- status — varchar(20) — NOT NULL
- attempt_count — integer — NOT NULL
- locked_until — timestamptz
- expires_at — timestamptz — NOT NULL
- verified_at — timestamptz
- created_at — timestamptz — NOT NULL
disposals
- serial_no — varchar(100) — Foreign key → medication_bottles.serial_no — NOT NULL
- prescription_no — varchar(100) — Foreign key → prescriptions.prescription_no
- disposer_session_key — uuid — Foreign key → auth_sessions.session_key — NOT NULL
- witness_session_key — uuid — Foreign key → auth_sessions.session_key — NOT NULL
- disposed_quantity — numeric(12,6) — NOT NULL
- reason — text — NOT NULL
- method — varchar(100) — NOT NULL
- evidence_object_key — varchar(500)
- status — varchar(20) — NOT NULL
- disposed_at — timestamptz
- disposal_type — varchar(50)
- disposal_place — varchar(200)
drug_products
- drug_code — varchar(100) — Primary key — NOT NULL
- product_name — varchar(200) — NOT NULL
- ingredient_name — varchar(200) — NOT NULL
- strength — varchar(100) — NOT NULL
- dosage_form — varchar(100) — NOT NULL
- package_quantity — numeric(12,6) — NOT NULL
- unit — varchar(30) — NOT NULL
- container_tare_weight_g — numeric(12,6)
- tolerance_quantity — numeric(12,6) — NOT NULL
- storage_condition — varchar(100)
- management_type — varchar(30)
institution_profile
- institution_id — bigint — Primary key — NOT NULL
- institution_name — varchar(200) — NOT NULL
- institution_code — varchar(100)
- office_phone_number — varchar(20)
- jurisdiction_name — varchar(200)
- jurisdiction_code — varchar(100)
- default_disposal_type — varchar(50)
- default_disposal_method — varchar(50)
- default_disposal_place — varchar(200)
medication_bottles
- serial_no — varchar(100) — Primary key — NOT NULL
- drug_code — varchar(100) — Foreign key → drug_products.drug_code — NOT NULL
- prescription_no — varchar(100) — Foreign key → prescriptions.prescription_no
- status — varchar(30) — NOT NULL
- created_at — timestamptz — NOT NULL
- lot_number — varchar(100)
- expiry_date — date
- barcode_rfid_code — varchar(200)
prescriptions
- prescription_no — varchar(100) — Primary key — NOT NULL
- patient_ref — varchar(200) — NOT NULL
- assigned_employee_no — varchar(50) — Foreign key → users.employee_no — NOT NULL
- drug_code — varchar(100) — Foreign key → drug_products.drug_code — NOT NULL
- prescribed_container_count — integer — NOT NULL
- prescribed_quantity — numeric(12,6) — NOT NULL
- unit — varchar(30) — NOT NULL
- prescribed_at — timestamptz — NOT NULL
- validation_status — varchar(20) — NOT NULL
- status — varchar(20) — NOT NULL
- single_dose_quantity — numeric(12,6)
- daily_frequency — integer
- total_days — integer
- use_session_key — uuid — Foreign key → auth_sessions.session_key
- before_weight_g — numeric(12,6)
- after_weight_g — numeric(12,6)
- actual_used_quantity — numeric(12,6)
- used_at — timestamptz
reports
- report_no — bigint — Primary key — NOT NULL
- prescription_no — varchar(100) — Foreign key → prescriptions.prescription_no — NOT NULL
- payload — jsonb — NOT NULL
- status — varchar(20) — NOT NULL
- file_object_key — varchar(500)
- pdf_title — varchar(255)
- original_file_name — varchar(500)
- file_content_type — varchar(100)
- file_size_bytes — bigint
- file_uploaded_at — timestamptz
- submitted_at — timestamptz
- created_at — timestamptz — NOT NULL
- disposal_serial_no — varchar(100) — Foreign key → disposals.serial_no — NOT NULL
- created_by_employee_no — varchar(50) — Foreign key → users.employee_no
- updated_at — timestamptz — NOT NULL
users
- employee_no — varchar(50) — Primary key — NOT NULL
- institution_id — bigint — Foreign key → institution_profile.institution_id — NOT NULL
- name — varchar(100) — NOT NULL
- department — varchar(100)
- role — varchar(30) — NOT NULL
- rfid_uid_hmac — varchar(128) — NOT NULL
- face_embedding — vector(512)
- phone_number — varchar(20) — NOT NULL
- nims_id — varchar(50) — NOT NULL
- nims_password_encrypted — varchar(500) — NOT NULL