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