Database Conventions

Table naming

Format: obelio_<plugin>_<entity> — snake_case, English, singular noun.

Plugin	Entity	Table name
ObelioCore	Patient	obelio_core_patient
ObelioScheduling	Appointment	obelio_scheduling_appointment
ObelioSurgery	Surgery session	obelio_sx_session
ObelioComms	Message	obelio_comms_message
ObelioConsents	Consent record	obelio_consents_record
ObelioAudit	Audit event	obelio_audit_event

Plugin abbreviations for long names:

Plugin	Abbreviation
ObelioSurgery	sx
ObelioOphthalmology	ophthalmology

Column naming

snake_case, English, no type prefixes:

-- Correct
`appointment_date`  DATE
`created_at`        DATETIME
`id_patient`        INT UNSIGNED
`is_active`         TINYINT(1)

-- Wrong
`appointmentDate`   -- camelCase
`dtAppointment`     -- type prefix
`fecha_cita`        -- Spanish

Constraint naming (ADR-027)

All constraints must be explicitly named using these prefixes:

Type	Format	Example
Primary key	pk_<table>	pk_obelio_scheduling_appointment
Foreign key	fk_<table>_<column>	fk_obelio_scheduling_appointment_id_patient
Unique	uq_<table>_<column>	uq_obelio_core_patient_code
Index	idx_<table>_<column>	idx_obelio_scheduling_appointment_date

Do not rely on auto-generated constraint names — they differ across MySQL versions and make migrations unpredictable.

Mandatory columns

Every obeliOmed table includes:

`idempresa`     INT             NOT NULL,   -- FacturaScripts company (tenant)
`codalmacen`    VARCHAR(4)      NOT NULL,   -- clinic center (FS warehouse)
`created_at`    DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at`    DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

Joins with FacturaScripts core

FS core tables follow a different naming convention (lowercase Spanish, abbreviated). Accept it as-is:

SELECT
    p.id,
    p.first_name,
    c.nombre AS company_name
FROM obelio_core_patient p
LEFT JOIN empresas c ON c.idempresa = p.idempresa   -- FS core table
WHERE p.codalmacen = :codalmacen
  AND p.idempresa  = :idempresa;

Never rename or alter FS core tables.

FacturaScripts Table XML

Every obeliOmed table must have a corresponding Table/<EntityName>.xml file. FacturaScripts reads these files to create and migrate the schema:

<?xml version="1.0" encoding="UTF-8"?>
<table>
    <column>
        <name>id</name>
        <type>serial</type>
    </column>
    <column>
        <name>idempresa</name>
        <type>integer</type>
        <null>NO</null>
    </column>
    <column>
        <name>codalmacen</name>
        <type>varchar(4)</type>
        <null>NO</null>
    </column>
    <column>
        <name>appointment_date</name>
        <type>date</type>
        <null>NO</null>
    </column>
    <constraint>
        <name>pk_obelio_scheduling_appointment</name>
        <type>PRIMARY KEY</type>
        <columns>id</columns>
    </constraint>
    <constraint>
        <name>fk_obelio_scheduling_appointment_idempresa</name>
        <type>FOREIGN KEY (idempresa) REFERENCES empresas (idempresa)</type>
    </constraint>
</table>

:::tip Column types in XML Use varchar(N) not character varying(N) — the FS XML parser expects MySQL-style type names. :::

Settings table pattern

Every plugin that has configurable values must create obelio_<plugin>_settings:

CREATE TABLE `obelio_scheduling_settings` (
    `id`            INT UNSIGNED    NOT NULL AUTO_INCREMENT,
    `idempresa`     INT             NOT NULL,
    `setting_key`   VARCHAR(100)    NOT NULL,
    `setting_value` TEXT,
    CONSTRAINT pk_obelio_scheduling_settings PRIMARY KEY (`id`),
    CONSTRAINT uq_obelio_scheduling_settings_key
        UNIQUE (`idempresa`, `setting_key`)
);

No value that a clinic admin might want to change is hardcoded in PHP. See ADR-034.