UZDI PostgreSQL Database Schema and Entity Relationships

The UZDI database is a PostgreSQL instance structured into six named schemas, each encapsulating one bounded domain of the system. This multi-schema layout prevents table-name collisions, makes permission management per domain straightforward, and mirrors the NestJS module boundaries one-to-one. TypeORM is configured with synchronize: false — the DDL script (DDL_UZDI_FINAL.sql) is the single authoritative source of truth for the physical schema; TypeORM only maps to it at runtime via entity decorators.

Schema Overview

Schema	Domain	Purpose
`estructura_institucional`	Institutional geography	Provinces, cantons, and UZDI office records
`seguridad`	Security & identity	Users (`prsn`), roles, profiles, permissions, sessions, audit history
`adolescente`	Core case management	Adolescent profiles, case files (expedientes), socio-educational measures, and reference catalogues
`contexto_familiar`	Family context	Legal representatives, kinship types, and adolescent–representative links
`talleres_actividades`	Workshops & activities	Workshop planning and attendance tracking
`gestion_documental`	Document management	Expedition documents and document-type catalogue

Key Entities by Schema

`estructura_institucional`

provincia

Top-level geographic division. Columns: prov_id, nombre. Parent of canton.

canton

Municipality within a province. Columns: cntn_id, prov_id (FK), nombre. Parent of uzdi and referenced by adolescente, representante.

uzdi

UZDI office record. Columns: uzdi_id, cntn_id (FK), nombre, zona, direccion, telefono, correo. Referenced by expediente, prsn, and taller.

`seguridad`

prsn (User)

System user. Columns: prsn_id, uzdi_id (FK), tppr_id (FK → tipo_persona), prsnnmbr, prsnapll, prsnsexo, prsndire, prsntelf, prsnmail, prsnlogn (unique), prsnpass, prsnobsr. The prsnpass column stores a bcrypt hash.

tipo_persona

Role catalogue seeded at install time. Columns: tppr_id, nombre. Pre-loaded values: Técnico (1), Coordinador (2), Administrativo (3), Director (4), Otro (5).

prfl / prms / accn

Profile-permission-action triad. prfl defines named profiles; accn defines discrete actions in modules; prms is the many-to-many join between profiles and actions with a unique constraint on (prfl_id, accn_id).

sesn

Session log. Columns: sesn_id, prsn_id (FK), prfl_id (FK), sesnfcin (login timestamp), sesnfcfn (logout timestamp).

historial

Immutable audit trail populated exclusively by triggers. Columns: hist_id, tabla_afectada, registro_id, usua_id, fecha, accion (INSERT / UPDATE / DELETE), estado_anterior (JSON text), estado_nuevo (JSON text), descripcion. No foreign keys — intentionally isolated to survive cascading deletes.

`adolescente`

adolescente

Core profile. Columns: adlc_id, cedula (unique), nombre, apellido, fecha_nac, sexo, nved_nombre, hijos, reincide (CHAR(1)), fecha_ingr, hijoPpl (CHAR(1)), observaciones, plus FKs to nacn_id, etcv_id, etna_id, cntn_id, gdo_id.

expediente

Operational case file linking an adolescent to a measure. Columns: expe_id, adlc_id (FK), uzdi_id (FK), infc_id (FK), mdso_id (FK), estado (CHECK IN ('En proceso','Atraso','Incomparecencia')), fecha_inicio_medida, fecha_fin_medida.

medida_socioeducativa

Catalogue of measure types (not the applied measure — that lives in expediente). Columns: mdso_id, nombre, duracion_meses (INT2, nullable). Used as a lookup FK from expediente.mdso_id.

infraccion

Legal infraction catalogue. Columns: infc_id, nombre, categoria, articulo_cona, descripcion. Referenced by expediente.infc_id.

Reference catalogues

Four lightweight lookup tables: nacionalidad (nacn_id, nombre), estado_civil (etcv_id, nombre), etnia (etna_id, nombre), gdo (gdo_id, nombre). All have fcCrea / fcMod audit timestamps.

`contexto_familiar`

representante

Legal guardian or family contact. Columns: repr_id, cntn_id (FK), nombre, apellido, cedula (unique), telefono, direccion.

tipo_parentesco

Kinship-type catalogue. Pre-loaded values: Madre, Padre, Abuela/o, Tía/o, Hermana/o, Cónyuge/Pareja, Tutor legal, Otro.

adolescente_representante

Association between an adolescent and a representative. Columns: adre_id, adlc_id (FK), repr_id (FK), tprt_id (FK → tipo_parentesco), es_referente (CHAR(1)), fecha_inicio, fecha_fin. Unique constraint on (adlc_id, repr_id).

`talleres_actividades`

taller

Workshop event. Columns: tall_id, uzdi_id (FK), prsn_id (FK), nombre, tema, objetivo, fecha, hora_inicio, hora_fin, lugar, observaciones. A CHECK constraint enforces hora_fin > hora_inicio.

asistencia_taller

Attendance record per participant. Columns: asta_id, tall_id (FK), adlc_id (FK, nullable), adre_id (FK → adolescente_representante, nullable), tipo_asistente, asistio (CHAR(1)). A mutually exclusive CHECK ensures exactly one of adlc_id / adre_id is set per row.

`gestion_documental`

tipo_documento

Document-type catalogue. Pre-loaded: Oficio judicial, Acta de comparecencia, Ficha de recepción, Informe técnico, Resolución judicial, Certificado médico, Certificado educativo, Otro.

documento_expediente

Document attached to an expedition. Columns: dcex_id, expe_id (FK, CASCADE on delete), prsn_id (FK), tpdc_id (FK → tipo_documento), es_habilitante (CHAR(1)), estado (CHECK IN ('pendiente','recibido','verificado','no aplica','rechazado')), numero_documento, fecha_documento, emisor, observaciones, fecha_registro.

Key Relationships

The diagram below describes the principal foreign-key chains across schemas:

estructura_institucional.provincia
  └─▶ canton
        └─▶ uzdi
              └─▶ seguridad.prsn (uzdi_id)
              └─▶ adolescente.expediente (uzdi_id)
              └─▶ talleres_actividades.taller (uzdi_id)

seguridad.tipo_persona
  └─▶ seguridad.prsn (tppr_id)

adolescente.adolescente (adlc_id)
  └─▶ adolescente.expediente (adlc_id)  [one-to-many]
        └─▶ adolescente.medida_socioeducativa (mdso_id)
        └─▶ adolescente.infraccion (infc_id)
        └─▶ estructura_institucional.uzdi (uzdi_id)
        └─▶ gestion_documental.documento_expediente (expe_id)

adolescente.adolescente (adlc_id)
  └─▶ contexto_familiar.adolescente_representante (adlc_id)
        └─▶ contexto_familiar.representante (repr_id)
        └─▶ contexto_familiar.tipo_parentesco (tprt_id)
        └─▶ talleres_actividades.asistencia_taller (adre_id)

TypeORM entity highlights

Adolescente — mapped to adolescente.adolescente. Eager relations on nacionalidad, estadoCivil, etnia, canton, and gdo mean those catalogues are always joined on a findOne / findAll call without explicit query options.
Expediente — mapped to adolescente.expediente. Eager relations on adolescente, uzdi, infraccion, and medida give a fully hydrated object in a single query.
MedidaSocioeducativa — mapped to adolescente.medida_socioeducativa. Acts as a pure catalogue (name + optional duration in months); the application instance of a measure is stored in expediente.
Uzdi — mapped to estructura_institucional.uzdi. Carries cntn_id as a plain @Column (no eager relation) to keep office listings lightweight.
User (prsn) — mapped to seguridad.prsn. The tppr_id column is exposed as a raw integer column, not as an eager relation, so the RBAC numeric comparison in the frontend router guard works directly from the serialized user object.

Audit System

Two PostgreSQL functions, installed in the seguridad schema, provide automatic auditing across all domain tables.

`fn_actualizar_fcmod()`

A BEFORE UPDATE trigger function that sets NEW."fcMod" = NOW() on every update. Installed on every table that carries a fcMod column (18 tables in total). This ensures the modification timestamp is always accurate regardless of whether the calling application supplies it.

`fn_registrar_historial()`

An AFTER INSERT OR UPDATE OR DELETE trigger function that writes a row to seguridad.historial on every data change. It:

Reads the current user ID from the PostgreSQL session variable app.usuario_id (set by the application before any DML).
Extracts the primary key of the affected row by scanning jsonb_each_text for a column ending in _id.
Inserts a record with the table name, operation type (INSERT / UPDATE / DELETE), before-state (to_jsonb(OLD)::TEXT), and after-state (to_jsonb(NEW)::TEXT).

seguridad.historial is excluded from its own trigger to prevent infinite recursion.

-- Example: trigger installed on adolescente.adolescente
CREATE TRIGGER trg_historial_adolescente
AFTER INSERT OR UPDATE OR DELETE ON adolescente.adolescente
FOR EACH ROW EXECUTE FUNCTION seguridad.fn_registrar_historial();

BOOLEAN Fields as `CHAR(1)`

Starting from DDL v6, all Boolean-style flags in the database are stored as CHAR(1) columns with a CHECK constraint allowing only 'V' (verdadero / true) or 'F' (falso / false). This affects adolescente.reincide, adolescente."hijoPpl", contexto_familiar.adolescente_representante.es_referente, talleres_actividades.asistencia_taller.asistio, and gestion_documental.documento_expediente.es_habilitante.TypeORM entities use the BooleanVF transformer (boolean-vf.transformer.ts) to transparently convert between the database 'V'/'F' character and the TypeScript boolean type on read and write. API consumers always work with true/false.

TypeORM Configuration

TypeORM is bootstrapped in AppModule with the following settings:

// UZDI_BACK/src/app.module.ts

TypeOrmModule.forRoot({
  type: 'postgres',
  url: process.env.DATABASE_URL,
  autoLoadEntities: true,
  // El esquema lo gobierna DDL_UZDI_FINAL.sql; TypeORM no debe alterarlo.
  synchronize: false,
}),

synchronize: false is intentional and must never be changed to true in any environment. All schema changes must be applied by running the updated DDL_UZDI_FINAL.sql script against the target database. TypeORM entities are mapping definitions only — they do not drive schema creation or migration.

Get Started

Core Concepts

Schema Overview

Key Entities by Schema

`estructura_institucional`

provincia

canton

uzdi

`seguridad`

prsn (User)

tipo_persona

prfl / prms / accn

sesn

historial

`adolescente`

adolescente

expediente

medida_socioeducativa

infraccion

Reference catalogues

`contexto_familiar`

representante

tipo_parentesco

adolescente_representante

`talleres_actividades`

taller

asistencia_taller

`gestion_documental`

tipo_documento

documento_expediente

Key Relationships

TypeORM entity highlights

Audit System

`fn_actualizar_fcmod()`

`fn_registrar_historial()`

BOOLEAN Fields as `CHAR(1)`

TypeORM Configuration

Build docs developers (and LLMs) love

Get Started

Core Concepts

Documentation Index

​Schema Overview

​Key Entities by Schema

​estructura_institucional

provincia

canton

uzdi

​seguridad

prsn (User)

tipo_persona

prfl / prms / accn

sesn

historial

​adolescente

adolescente

expediente

medida_socioeducativa

infraccion

Reference catalogues

​contexto_familiar

representante

tipo_parentesco

adolescente_representante

​talleres_actividades

taller

asistencia_taller

​gestion_documental

tipo_documento

documento_expediente

​Key Relationships

​TypeORM entity highlights

​Audit System

​fn_actualizar_fcmod()

​fn_registrar_historial()

​BOOLEAN Fields as CHAR(1)

​TypeORM Configuration

Build docs developers (and LLMs) love

Schema Overview

Key Entities by Schema

`estructura_institucional`

`seguridad`

`adolescente`

`contexto_familiar`

`talleres_actividades`

`gestion_documental`

Key Relationships

TypeORM entity highlights

Audit System

`fn_actualizar_fcmod()`

`fn_registrar_historial()`

BOOLEAN Fields as `CHAR(1)`

TypeORM Configuration