IN PROGRESS

Schema

https://drive.google.com/file/d/1phygA8_ulSjZAJ7KwFVfEgVq7vch4qbm/view?usp=sharing

Table Desctiptions

edr_data

(edr_id), unique

Name	Type	M/O
id	uuid	M
edr_id	integer	M
name	varchar	M
short_name	varchar	O
public_name	varchar	M
legal_form	varchar	O
edrpou	varchar	M
kveds	jsonb	M
registration_address	jsonb	M
is_active	boolean	M
state	integer	M
inserted_at	timestamp	M
inserted_by	uuid	M
updated_at	timestamp	M
updated_by	uuid	M

legal_entitities

PK(id), unique

(edr_data_id, type) must be unique when status <> closed and is_active = true

Name	Type	M/O	Description and constraints	Status
id	uuid	M
name	varchar(255)	O		DEPRECATED
short_name	varchar(255)	O		DEPRECATED
public_name	varchar(255)	O		DEPRECATED
owner_property_type	varchar(255)	O		DEPRECATED
legal_form	varchar(255)	O		DEPRECATED
kveds	jsonb	O		DEPRECATED
registration_address	jsonb	O	object Addresses	DEPRECATED
edr_data_id	uuid	M	FK(edr_data)
type	varchar	M	Dictionary LEGAL_ENTITY_TYPE PRIMARY_CARE, OUTPATIENT, EMERGENCY, PHARMACY MSP, MSP_PHARMACY (could not be created or updated in e-Health, should be processed as PRIMARY_CARE, then should be deprecated)
edrpou	varchar	M
accreditation	jsonb	O	array of object accreditation
residence_address	jsonb	O	object Addresses
phones	jsonb	M	array of object Phones
email	varchar	M
is_active	boolean	M
website	varchar	O
receiver_funds_code	varchar	O
archive	jsonb	O	array of object Table specs#available_time
created_by_mis_client_id	uuid	O
nhs_verified	boolean	M
nhs_reviewed	boolean	M
inserted_at	timestamp	M
inserted_by	uuid	M
updated_at	timestamp	M
updated_by	uuid	M
license_id	uuid	O	FK(licenses)
status	boolean	M	active, closed, suspended, reorganised
status_reason	varchar	O
nhs_verified_change_date	timestamp	O

licenses

PK(id), unique

(edr_data_id, type), unique

Name	Type	M/O	Description and constraints
id	uuid	M
license_number	varchar	O
type	varchar	M	`Dictionary LICENSE_TYPE`
issued_by	varchar	M
issued_date	date	M
issuer_status	varchar	O
expiry_date	date	O
active_from_date	date	M
what_licensed	text	O
order_no	varchar	M
is_active	boolean	M
inserted_at	timestamp	M
inserted_by	uuid	M
updated_at	timestamp	M
updated_by	uuid	M

employees

PK(id), unique

(legal_entity_id, employee_type, is_active = true, employee_type = OWNER OR PHARMACY_OWNER), unique

Name	Type	M/O	Description and constraints
id	uuid	M
position	varchar	M	Dictionary: position
status	varchar	M	APPROVED, DISMISSED
status_reason	varchar	O
employee_type	varchar	M	Dictionary: employee_type
is_active	boolean	M	by default false
inserted_at	timestamp	M
inserted_by	uuid	M
updated_at	timestamp	M
updated_by	uuid	M
start_date	date	M
end_date	date	O
legal_entity_id	uuid	M
division_id	uuid	MO
party_id	uuid	M
additional_info	jsonb	O
speciality	jsonb	O

divisions

PK(id), unique

Name	Type	M/O	Description and constraints
id	uuid	M
external_id	varchar	O
name	varchar	M
type	varchar	M	Dictionary: division_type
mountain_group	boolean	M
phones	jsonb	M
email	varchar	O
legal_entity_id	uuid	M
location	geometry	O
status	varchar	M	ACTIVE, INACTIVE
working_hours	jsonb	O	object Table specs#available_time
dls_id	varchar	O
dls_verified	boolean	O
is_active	boolean	M	by default = true
inserted_at	timestamp	M
inserted_by	uuid	M
updated_at	timestamp	M
updated_by	uuid	M

healthcare_services

PK(id), unique

(division_id, speciality_type, providing_conditions), unique

Name	Type	M/O	Description and constraints	Status
id	uuid	M
legal_entity_id	uuid	M	reference to legal entity
division_id	uuid	M	reference to division
speciality_type	varchar	M	Dictionary: speciality_type
providing_conditions	varchar	M	Dictionary: INPATIENT, OUTPATIENT, HOME, FIELD
category	[uuid]	O	array of references to service_group with parent_group_id = null	No Implementation yet
type	[uuid]	O	array of references to array of references to service_group with no grandparents	No Implementation yet
status	varchar	M	ACTIVE, INACTIVE Available transitions: Active → Inactive Inactive → Active
coverage_area	[uuid]	O	array of references to divisions	No Implementation yet
comment	varchar	O
available_time	jsonb	O	array of object Table specs#available_time
restricted_periods	jsonb	O	object Table specs#restricted_periods
is_active	boolean	M	by default = true
inserted_at	timestamp	M
inserted_by	uuid	M
updated_at	timestamp	M
updated_by	uuid	M

healthcare_services_status_hstr

PK(id), unique

Name	Type	M/O	Description and constraints
id	uuid	M
healthcare_service_id	uuid	M	reference to healthcare service
status	varchar	M
inserted_at	timestamp	M

equipment

PK(id), unique

Name	Type	M/O	Description and constraints	Status
id	uuid	M		ok
status	varchar	M	active, inactive Available transitions: Active → Inactive Inactive → Active	ok
type	jsonb	M	object CodeableConcept	ok
external_id	varchar	M		Not in FHIR, ok
division_id	uuid	M	reference to division	ok
legal_entity_id	uuid	M	reference to legal entity	ok
udi	jsonb	O	array of objects. Contains unique identifiers assigned to the asset by the manufacturer (like IMEI for phones) Object fields: value - string, required. type - string, required (EQUIPMENT_UDI_TYPE dict) assigner_name - string, optional	ok
lot_number	varchar	O		ok
manufacturer	varchar	O		ok
manufacture_date	date	O	date	ok
expiration_date	date	O	date	ok
model_number	varchar	O		ok
part_number	varchar	O		ok
version	varchar	O		ok
name	varchar	O		ok
serial_number	varchar	O		ok
note	varchar	O		ok
is_active	boolean	M	by default = true	ok
inserted_at	timestamp	M		ok
inserted_by	uuid	M		ok
updated_at	timestamp	M		ok
updated_by	uuid	M		ok

equipment_status_hstr

PK(id), unique

Name	Type	M/O	Description and constraints
id	uuid	M
equipment_id	uuid	M	reference to equipment
status	varchar	M
inserted_at	timestamp	M
inserted_by	uuid	M

employee_roles

PK(id), unique

(employee_id,healthcare_service_id, status=active), unique

Name	Type	M/O	Description and constraints
id	uuid	M
healthcare_service_id	uuid	M	reference to healthcare service
employee_id	uuid	M	reference to employee
start_date	timestamp	M	fullfilled automatically when new record is created
end_date	timestamp	O	fullfilled automatically on deactivating record
status	varchar	M	ACTIVE, INACTIVE Available transition: Active → Inactive
is_active	boolean	M	by default = true
inserted_at	timestamp	M
inserted_by	uuid	M
updated_at	timestamp	M
updated_by	uuid	M

licensed_healthcare_services

PK(id), unique

(healthcare_service_id, status=active), unique

Name	Type	M/O	Description and constraints
id	uuid	M
healthcare_service_id	uuid	M	FK on healthcare_services.id
status	varchar	M	ACTIVE, INACTIVE Available transition: Active → Inactive Inactive → Active
is_active	boolean	M	by default = true
inserted_at	timestamp	M
inserted_by	uuid	M
updated_at	timestamp	M
updated_by	uuid	M

licensed_healthcare_services_status_hstr

PK(id), unique

Name	Type	M/O	Description and constraints
id	uuid	M
licensed_healthcare_service_id	uuid	M	reference to licensed healthcare service
status	varchar	M
inserted_at	timestamp	M

Objects

Addresses

see https://uaehealthapi.docs.apiary.io/#reference/dummy-methods/dummy-for-legal-entity/create/update-legal-entity

Name	type	M/O	Description and constraints

Phones

Name	Type	M/O	Description and constraints
type	string	M	`Dictionary PHONE_TYPE` type of phone Land Line or Mobile. At least one of type must be present. Each type can be represented only once. MOBILE
number	string	M	phone number in format '+38/' +380503410870

Accreditation

Name	Type	M/O	Description and constraints
category		M
issued_date		O
expiry_date		O
order_no		M
order_date		M

available_time

Name	Type	M/O	Description and constraints
days_of_week	[varchar]	M	values: mon \| tue \| wed \| thu \| fri \| sat \| sun
all_day	boolean	O
available_start_time	varchar	O
available_end_time	varchar	O

restricted_periods

Name	Type	M/O	Description and constraints
description	varchar	M	reason why service is not available
during	jsonb	M	object period

period

Name	Type	M/O	Description and constraints
start	timestamp	M
end	timestamp	M

E-Health

Table specs

Schema

Table Desctiptions

edr_data

legal_entitities

licenses

employees

divisions

healthcare_services

healthcare_services_status_hstr

equipment

equipment_status_hstr

employee_roles

licensed_healthcare_services_status_hstr

Objects

Addresses

Phones

Archive

Accreditation

available_time

restricted_periods

period

Name	Type	M/O	Description and constraints
date	string	M	the date when paper documents were transferred to archive 2017-02-28
place	string	M	the address of building where paper documents are вул. Грушевського 15