ObelioScheduling — Database Schema

:::info Auto-generated This page is generated automatically from the Table/*.xml files in obeliOmed/OftalmolAgenda on every merge to develop. Last updated: 2026-06-20. :::

Table overview

9 tables defined by this plugin.

Table	Columns	Constraints
oft_appointment_resource_links	4	3
oft_appointment_resource_types	7	1
oft_appointment_resources	8	1
oft_appointment_rooms	4	1
oft_appointment_status	3	1
oft_appointment_types	5	1
oft_appointments	15	4
oft_specialist_schedule	8	2
oft_specialist_unavailability	9	2

:::note Naming conventions

• Constraints follow ADR-027 prefix pattern: pk_ primary key, fk_ foreign key, uq_ unique, idx_ index.
• Table names follow ADR-025 rename: obelio_<plugin>_* (legacy oft_* tables migrated during v2 refactor).

:::

Table details

oft_appointment_resource_links

Column	Type	Null	Default
id	serial	NO	—
idAppointment	integer	NO	—
idResource	integer	NO	—
creationDate	timestamp	YES	—

Foreign keys:

• oft_appointment_resource_links_appointment_fk: FOREIGN KEY (idAppointment) REFERENCES oft_appointments (id) ON DELETE CASCADE ON UPDATE CASCADE
• oft_appointment_resource_links_resource_fk: FOREIGN KEY (idResource) REFERENCES oft_appointment_resources (id) ON DELETE RESTRICT ON UPDATE CASCADE

Unique constraints:

• oft_appointment_resource_links_unique: UNIQUE (idAppointment, idResource)

oft_appointment_resource_types

Column	Type	Null	Default
code	varchar(40)	NO	—
name	varchar(120)	NO	—
description	text	YES	—
icon	varchar(80)	YES	—
sort_order	integer	YES	0
creationDate	timestamp	YES	—
modificationDate	timestamp	YES	—

Primary key: PRIMARY KEY (code)

oft_appointment_resources

Column	Type	Null	Default
id	serial	NO	—
idTypeCode	varchar(40)	NO	—
idOrigin	integer	YES	—
name	varchar(160)	NO	—
code	varchar(40)	YES	—
active	boolean	YES	true
creationDate	timestamp	YES	—
modificationDate	timestamp	YES	—

Foreign keys:

• oft_appointment_resources_type_fk: FOREIGN KEY (idTypeCode) REFERENCES oft_appointment_resource_types (code) ON DELETE RESTRICT ON UPDATE CASCADE

oft_appointment_rooms

Column	Type	Null	Default
id	serial	NO	—
idSpeciality	int	YES	—
name	varchar(250)	NO	—
description	varchar(250)	NO	—

Primary key: PRIMARY KEY (id)

oft_appointment_status

Column	Type	Null	Default
id	serial	NO	—
name	varchar(250)	NO	—
color	varchar(20)	NO	—

Primary key: PRIMARY KEY (id)

oft_appointment_types

Column	Type	Null	Default
id	serial	NO	—
idSpeciality	int	YES	—
idReason	int	YES	—
name	varchar(250)	NO	—
duration	int	YES	30

Primary key: PRIMARY KEY (id)

oft_appointments

Column	Type	Null	Default
id	serial	NO	—
idPatient	int	NO	—
idSpeciality	int	NO	—
idSpecialist	int	NO	—
idRoom	int	YES	—
idLinkedRecord	int	YES	—
linkedRecordType	varchar(40)	YES	—
appointmentDate	datetime	NO	—
duration	int	YES	30
status	varchar(20)	YES	'pending'
type	int	YES	—
notes	text	YES	—
nick	varchar(50)	NO	—
creationDate	datetime	NO	CURRENT_TIMESTAMP
modificationDate	datetime	NO	CURRENT_TIMESTAMP

Primary key: PRIMARY KEY (id)

Foreign keys:

• fk_oft_appointments_patient: FOREIGN KEY (idPatient) REFERENCES oft_patients (idPatient) ON DELETE CASCADE ON UPDATE CASCADE
• fk_oft_appointments_specialist: FOREIGN KEY (idSpecialist) REFERENCES oft_specialists (id) ON DELETE CASCADE ON UPDATE CASCADE
• fk_oft_appointments_clinic: FOREIGN KEY (idClinic) REFERENCES oft_clinics (id) ON DELETE SET NULL ON UPDATE CASCADE

oft_specialist_schedule

Column	Type	Null	Default
id	serial	NO	—
idSpecialist	int	NO	—
dayOfWeek	smallint	NO	—
startTime	time	NO	—
endTime	time	NO	—
slotDuration	int	YES	30
isActive	boolean	YES	true
creationDate	datetime	NO	CURRENT_TIMESTAMP

Primary key: PRIMARY KEY (id)

Foreign keys:

• fk_oft_specialist_schedule_specialist: FOREIGN KEY (idSpecialist) REFERENCES oft_specialists (id) ON DELETE CASCADE ON UPDATE CASCADE

oft_specialist_unavailability

Column	Type	Null	Default
id	serial	NO	—
idSpecialist	int	NO	—
startDate	date	NO	—
endDate	date	NO	—
startTime	time	YES	—
endTime	time	YES	—
type	varchar(30)	YES	—
notes	text	YES	—
creationDate	datetime	NO	CURRENT_TIMESTAMP

Primary key: PRIMARY KEY (id)

Foreign keys:

• fk_oft_specialist_unavailability_specialist: FOREIGN KEY (idSpecialist) REFERENCES oft_specialists (id) ON DELETE CASCADE ON UPDATE CASCADE