ObelioCore — Database Schema
:::info Auto-generated
This page is generated automatically from the Table/*.xml files in
obeliOmed/OftalmolBase on every merge to develop.
Last updated: 2026-06-20.
:::
Table overview
19 tables defined by this plugin.
:::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>_*(legacyoft_*tables migrated during v2 refactor).
:::
Table details
obelio_core_episodes
| Column | Type | Null | Default |
|---|---|---|---|
id | serial | YES | — |
idExpedient | int | NO | — |
idReason | int | YES | — |
title | varchar(120) | YES | — |
description | text | YES | — |
status | varchar(20) | NO | — |
priority | varchar(10) | NO | — |
openedAt | datetime | NO | CURRENT_TIMESTAMP |
modifiedAt | datetime | NO | CURRENT_TIMESTAMP |
closedAt | datetime | YES | — |
idUserOpenedBy | varchar(50) | YES | — |
idUserClosedBy | varchar(50) | YES | — |
closureNote | text | YES | — |
idAssignedSpecialist | int | YES | — |
Primary key: PRIMARY KEY (id)
Foreign keys:
obelio_core_episodes_idExpedient_fkey: FOREIGN KEY (idExpedient) REFERENCES obelio_core_expedients (id) ON DELETE CASCADE ON UPDATE CASCADEobelio_core_episodes_idReason_fkey: FOREIGN KEY (idReason) REFERENCES oft_reasons (id) ON DELETE SET NULL ON UPDATE CASCADEobelio_core_episodes_idUserOpenedBy_fkey: FOREIGN KEY (idUserOpenedBy) REFERENCES users (nick) ON DELETE SET NULL ON UPDATE CASCADEobelio_core_episodes_idUserClosedBy_fkey: FOREIGN KEY (idUserClosedBy) REFERENCES users (nick) ON DELETE SET NULL ON UPDATE CASCADEobelio_core_episodes_idAssignedSpecialist_fkey: FOREIGN KEY (idAssignedSpecialist) REFERENCES oft_specialists (id) ON DELETE SET NULL ON UPDATE CASCADE
obelio_core_expedients
| Column | Type | Null | Default |
|---|---|---|---|
id | serial | YES | — |
idPatient | int | NO | — |
idSpeciality | int | NO | — |
idEmpresa | int | YES | — |
status | varchar(20) | NO | — |
openedAt | datetime | NO | CURRENT_TIMESTAMP |
modifiedAt | datetime | NO | CURRENT_TIMESTAMP |
closedAt | datetime | YES | — |
idUserOpenedBy | varchar(50) | YES | — |
idUserClosedBy | varchar(50) | YES | — |
idAssignedSpecialist | int | YES | — |
closeReason | text | YES | — |
professionalPrivateNotes | text | YES | — |
tags | text | YES | — |
Primary key: PRIMARY KEY (id)
Foreign keys:
obelio_core_expedients_idPatient_fkey: FOREIGN KEY (idPatient) REFERENCES oft_patients (idPatient) ON DELETE RESTRICT ON UPDATE CASCADEobelio_core_expedients_idSpeciality_fkey: FOREIGN KEY (idSpeciality) REFERENCES oft_specialities (id) ON DELETE RESTRICT ON UPDATE CASCADEobelio_core_expedients_idEmpresa_fkey: FOREIGN KEY (idEmpresa) REFERENCES empresas (idempresa) ON DELETE SET NULL ON UPDATE CASCADEobelio_core_expedients_idUserOpenedBy_fkey: FOREIGN KEY (idUserOpenedBy) REFERENCES users (nick) ON DELETE SET NULL ON UPDATE CASCADEobelio_core_expedients_idUserClosedBy_fkey: FOREIGN KEY (idUserClosedBy) REFERENCES users (nick) ON DELETE SET NULL ON UPDATE CASCADEobelio_core_expedients_idAssignedSpecialist_fkey: FOREIGN KEY (idAssignedSpecialist) REFERENCES oft_specialists (id) ON DELETE SET NULL ON UPDATE CASCADE
Unique constraints:
obelio_core_expedients_idPatient_idSpeciality_idEmpresa_key: UNIQUE (idPatient, idSpeciality, idEmpresa)
obelio_patient_specialty_history
| Column | Type | Null | Default |
|---|---|---|---|
id | serial | NO | — |
idpatient | integer | NO | — |
codspecialty | varchar(10) | NO | `` |
history_text | text | YES | — |
updated_at | timestamp | YES | CURRENT_TIMESTAMP |
updated_by | varchar(50) | YES | — |
Primary key: PRIMARY KEY (id)
Foreign keys:
obelio_patient_specialty_history_idpatient_fkey: FOREIGN KEY (idpatient) REFERENCES oft_patients (idPatient) ON DELETE CASCADE ON UPDATE CASCADE
Unique constraints:
obelio_patient_specialty_history_idpatient_codspecialty_key: UNIQUE (idpatient, codspecialty)
oft_file_attachments
| Column | Type | Null | Default |
|---|---|---|---|
id | serial | YES | — |
fileName | text | NO | — |
filePath | text | NO | — |
fileType | varchar(50) | NO | — |
uploadDate | datetime | NO | CURRENT_TIMESTAMP |
nick | varchar(50) | NO | — |
generalNote | text | YES | — |
Primary key: PRIMARY KEY (id)
oft_file_attachments_links
| Column | Type | Null | Default |
|---|---|---|---|
id | serial | YES | — |
idFile | int | NO | — |
idSpeciality | int | NO | — |
idExpedient | int | YES | — |
idPatient | int | NO | — |
idFileType | varchar(50) | YES | — |
idTestRecord | int | YES | — |
idTestType | int | YES | — |
Primary key: PRIMARY KEY (id)
Foreign keys:
fk_file_attachment_links_file_attachments: FOREIGN KEY (idFile) REFERENCES oft_file_attachments (id) ON DELETE CASCADE ON UPDATE CASCADEoft_file_attachments_links_idExpedient_fkey: FOREIGN KEY (idExpedient) REFERENCES obelio_core_expedients (id) ON DELETE CASCADE ON UPDATE CASCADEfk_file_attachments_links_patient: FOREIGN KEY (idPatient) REFERENCES oft_patients (idPatient) ON DELETE RESTRICT ON UPDATE CASCADE
oft_filetypes
| Column | Type | Null | Default |
|---|---|---|---|
id | serial | YES | — |
idSpeciality | int | NO | — |
nick | varchar(50) | NO | — |
creationDate | datetime | NO | CURRENT_TIMESTAMP |
idValidity | int | YES | — |
fileName | character varying(100) | NO | — |
generalNote | text | YES | — |
Primary key: PRIMARY KEY (id)
Foreign keys:
oft_filetypes_validities_fk: FOREIGN KEY (idValidity) REFERENCES oft_filetypes_validities(id)
oft_filetypes_validities
| Column | Type | Null | Default |
|---|---|---|---|
id | serial | YES | — |
name | varchar(100) | NO | — |
duration | varchar(50) | NO | — |
Primary key: PRIMARY KEY (id)
oft_insurance_company
| Column | Type | Null | Default |
|---|---|---|---|
id | serial | YES | — |
idSpeciality | int | NO | — |
nick | varchar(50) | NO | — |
creationDate | datetime | NO | CURRENT_TIMESTAMP |
companyName | character varying(100) | NO | — |
Primary key: PRIMARY KEY (id)
oft_logs
| Column | Type | Null | Default |
|---|---|---|---|
id | serial | NO | — |
idPatient | integer | NO | — |
idExpedient | integer | NO | — |
idTestType | integer | NO | — |
model | character varying(100) | YES | — |
nick | character varying(50) | YES | — |
channel | character varying(40) | YES | — |
level | character varying(15) | NO | — |
ip | character varying(40) | YES | — |
message | text | YES | — |
context | text | YES | — |
time | timestamp | NO | — |
uri | character varying(200) | YES | — |
Primary key: PRIMARY KEY (id)
oft_notes
| Column | Type | Null | Default |
|---|---|---|---|
id | serial | YES | — |
idSpeciality | int | NO | — |
idExpedient | int | NO | — |
idEpisode | int | YES | — |
idNoteType | int | NO | — |
nick | varchar(50) | NO | — |
creationDate | datetime | NO | CURRENT_TIMESTAMP |
modificationDate | datetime | NO | CURRENT_TIMESTAMP |
note | text | NO | — |
generalNote | text | YES | — |
profesionalNote | text | YES | — |
Primary key: PRIMARY KEY (id)
Foreign keys:
oft_notes_idExpedient_fkey: FOREIGN KEY (idExpedient) REFERENCES obelio_core_expedients (id) ON DELETE CASCADE ON UPDATE CASCADEoft_notes_idEpisode_fkey: FOREIGN KEY (idEpisode) REFERENCES obelio_core_episodes (id) ON DELETE SET NULL ON UPDATE CASCADE
oft_patients
| Column | Type | Null | Default |
|---|---|---|---|
idPatient | serial | YES | — |
codcliente | varchar(10) | YES | — |
idContact | int | YES | — |
nick | varchar(50) | NO | — |
firstName | varchar(60) | YES | — |
lastName1 | varchar(60) | YES | — |
lastName2 | varchar(60) | YES | — |
gender | varchar(1) | YES | — |
documentType | varchar(10) | YES | — |
nif | varchar(10) | YES | — |
birthDate | Date | YES | — |
age | integer | YES | — |
idioma | varchar(5) | YES | — |
phoneNumber | varchar(20) | YES | — |
phoneNumber2 | varchar(20) | YES | — |
email | varchar(120) | YES | — |
job | varchar(120) | YES | — |
idInsurance | int | YES | — |
numInsurance | varchar(20) | YES | — |
insuranceCompanyName | varchar(100) | YES | — |
allergies | text | YES | — |
diseases | text | YES | — |
medication | text | YES | — |
surgeries | text | YES | — |
personalHistory | text | YES | — |
familyHistory | text | YES | — |
habits | text | YES | — |
emergencyContactName | varchar(100) | YES | — |
emergencyContactPhone | varchar(20) | YES | — |
emergencyContactRelation | varchar(20) | YES | — |
observations_general | text | YES | — |
professional_private_notes | text | YES | — |
consentMarketing | boolean | NO | false |
consentDataShare | boolean | NO | false |
portalAccountEnabled | boolean | NO | false |
portalLastLogin | timestamp | YES | — |
Primary key: PRIMARY KEY (idPatient)
Foreign keys:
oft_patients_idContact_fkey: FOREIGN KEY (idContact) REFERENCES contactos (idcontacto) ON DELETE SET NULL ON UPDATE CASCADEoft_patients_idInsurance_fkey: FOREIGN KEY (idInsurance) REFERENCES oft_insurance_company (idInsurance) ON DELETE SET NULL ON UPDATE CASCADE
oft_printtemplates
| Column | Type | Null | Default |
|---|---|---|---|
id | serial | NO | — |
name | character varying(50) | NO | — |
label | character varying(100) | NO | — |
paper_size | character varying(20) | YES | — |
orientation | character varying(5) | YES | 'P' |
header_height | integer | YES | — |
footer_height | integer | YES | — |
left_margin | integer | YES | 10 |
right_margin | integer | YES | 10 |
Primary key: PRIMARY KEY (id)
oft_reasons
| Column | Type | Null | Default |
|---|---|---|---|
id | serial | YES | — |
idSpeciality | int | NO | — |
nick | varchar(50) | NO | — |
creationDate | datetime | NO | CURRENT_TIMESTAMP |
OMScode | text | YES | — |
name | character varying(100) | NO | — |
description | text | YES | — |
Primary key: PRIMARY KEY (id)
oft_reviews
| Column | Type | Null | Default |
|---|---|---|---|
id | serial | YES | — |
idSpeciality | int | NO | — |
nick | varchar(50) | NO | — |
creationDate | datetime | NO | CURRENT_TIMESTAMP |
reviewName | character varying(100) | NO | — |
generalNote | text | YES | — |
Primary key: PRIMARY KEY (id)
oft_specialists
| Column | Type | Null | Default |
|---|---|---|---|
id | serial | NO | — |
idSpeciality | integer | NO | — |
codAgente | integer | YES | — |
creationDate | datetime | NO | CURRENT_TIMESTAMP |
name | character varying(100) | NO | — |
nif | character varying(10) | YES | — |
address | character varying(100) | YES | — |
phoneNumber | int | YES | — |
email | character varying(100) | YES | — |
codpostal | integer | YES | — |
town | character varying(100) | YES | — |
province | character varying(100) | YES | — |
numcolegiado | character varying(20) | YES | — |
colegioProfesional | character varying(100) | YES | — |
sign | character varying(100) | YES | — |
notes | character varying(100) | YES | — |
Primary key: PRIMARY KEY (id)
oft_specialities
| Column | Type | Null | Default |
|---|---|---|---|
id | serial | YES | — |
idSpeciality | varchar(50) | NO | — |
specialityName | varchar(75) | NO | — |
Primary key: PRIMARY KEY (id)
oft_tests
| Column | Type | Null | Default |
|---|---|---|---|
id | serial | YES | — |
idSpeciality | int | NO | — |
testName | character varying(100) | NO | — |
testType | int | NO | — |
Primary key: PRIMARY KEY (id)
oft_treatment_reasons
| Column | Type | Null | Default |
|---|---|---|---|
idTreatment | int | NO | — |
idReason | int | NO | — |
Primary key: PRIMARY KEY (idTreatment, idReason)
Foreign keys:
fk_oft_treatment_reasons_treatment: FOREIGN KEY (idTreatment) REFERENCES oft_treatments (id) ON DELETE CASCADE ON UPDATE CASCADEfk_oft_treatment_reasons_reasons: FOREIGN KEY (idReason) REFERENCES oft_reasons (id) ON DELETE RESTRICT ON UPDATE CASCADE
oft_treatments
| Column | Type | Null | Default |
|---|---|---|---|
id | serial | YES | — |
idSpeciality | int | NO | — |
idExpedient | int | NO | — |
idReview | int | YES | — |
nick | varchar(50) | NO | — |
creationDate | datetime | NO | CURRENT_TIMESTAMP |
modificationDate | datetime | NO | CURRENT_TIMESTAMP |
treatment | text | NO | — |
generalNote | text | YES | — |
profesionalNote | text | YES | — |
Primary key: PRIMARY KEY (id)
Foreign keys:
oft_treatments_idExpedient_fkey: FOREIGN KEY (idExpedient) REFERENCES obelio_core_expedients (id) ON DELETE RESTRICT ON UPDATE CASCADEfk_oft_treatments_review: FOREIGN KEY (idReview) REFERENCES oft_reviews (id) ON DELETE SET NULL ON UPDATE CASCADE