Skip to main content

Documentation Index

Fetch the complete documentation index at: https://mintlify.com/Zapiony/PUCE_UZDI_2026/llms.txt

Use this file to discover all available pages before exploring further.

The UZDI database is a PostgreSQL instance structured across six named schemas, each corresponding to a distinct business domain. The entire schema is defined in a single DDL file — DDL_UZDI_FINAL.sql — located at the repository root. TypeORM is configured with synchronize: false, meaning the database structure is always governed by this DDL file and never auto-generated by the ORM.

​
Initialization

1

Create the database

Connect to your PostgreSQL server and create the application database:
psql -U postgres -c "CREATE DATABASE uzdi_db;"
Or using the psql interactive shell:
CREATE DATABASE uzdi_db;
2

Apply the DDL script

Run the DDL file from the repository root against the new database. This creates all six schemas, tables, constraints, indexes, and trigger functions in a single pass:
psql -U postgres -d uzdi_db -f DDL_UZDI_FINAL.sql
The script is idempotent for schema creation (CREATE SCHEMA IF NOT EXISTS) but not for tables — do not run it twice against a database that already has data unless you first drop the schemas using the commented DROP SCHEMA … CASCADE statements at the top of the file.
3

Verify the schemas were created

Connect to the database and list the schemas:
psql -U postgres -d uzdi_db

\dn
You should see all six application schemas listed alongside the default public schema:
      List of schemas
          Name               |  Owner
-------------------------------+----------
 adolescente                  | postgres
 contexto_familiar            | postgres
 estructura_institucional     | postgres
 gestion_documental           | postgres
 public                       | postgres
 seguridad                    | postgres
 talleres_actividades         | postgres

​
Schema Reference

SchemaPurposeKey tables
estructura_institucionalGeographic and organizational hierarchyprovincia, canton, uzdi
seguridadUsers, profiles, permissions, sessions, and audit historyprsn, prfl, accn, prms, sesn, historial, tipo_persona
adolescenteCore adolescent registry and case managementadolescente, expediente, medida_socioeducativa, infraccion, etnia, nacionalidad, estado_civil, gdo
contexto_familiarFamily representatives and relationship recordsrepresentante, adolescente_representante, tipo_parentesco
talleres_actividadesWorkshops and attendance trackingtaller, asistencia_taller
gestion_documentalExpedient document managementdocumento_expediente, tipo_documento

​
DDL Conventions

​
BOOLEAN as CHAR(1) ('V'/'F')

All boolean fields in the database are stored as CHAR(1) with a CHECK constraint that allows only 'V' (verdadero / true) or 'F' (falso / false). Native SQL BOOLEAN is not used. 
-- Example from adolescente.adolescente
reincide  CHAR(1) NOT NULL DEFAULT 'F',
"hijoPpl" CHAR(1) NOT NULL DEFAULT 'F',

CONSTRAINT chk_adolescente_reincide  CHECK (reincide  IN ('V', 'F')),
CONSTRAINT chk_adolescente_hijoppl   CHECK ("hijoPpl" IN ('V', 'F'))
TypeORM entities use the BooleanVF value transformer (defined in src/common/transformers/boolean-vf.transformer.ts) to convert transparently between 'V'/'F' at the database layer and true/false in TypeScript. See the Backend Modules page for usage details.

​
expediente.estado CHECK Constraint

The estado column on adolescente.expediente is restricted to exactly three string values: 
CONSTRAINT chk_expediente_estado
    CHECK (estado IN ('En proceso', 'Atraso', 'Incomparecencia'))
ValueMeaning
'En proceso'Measure is active and progressing on schedule
'Atraso'Adolescent is behind on fulfilling the measure
'Incomparecencia'Adolescent has not presented as required
Any INSERT or UPDATE that supplies a different value will be rejected by PostgreSQL. DTOs must validate against these exact strings.

​
Timestamp Columns (fcCrea / fcMod)

Most tables include "fcCrea" TIMESTAMP DEFAULT NOW() and "fcMod" TIMESTAMP DEFAULT NOW(). The fcMod column is kept current automatically by the fn_actualizar_fcmod trigger (see below). Note that these column names are quoted identifiers with camelCase — they must be quoted in raw SQL queries.

​
Audit Functions and Triggers

The DDL defines two PostgreSQL trigger functions in the seguridad schema that fire automatically on DML operations.

​
seguridad.fn_registrar_historial

Fires AFTER INSERT OR UPDATE OR DELETE on every audited table (all tables except seguridad.historial itself, to prevent recursion). It writes one row to seguridad.historial for every affected row: 
INSERT INTO seguridad.historial (
    tabla_afectada,   -- 'schema.table_name' (e.g. 'adolescente.expediente')
    registro_id,      -- PK value of the affected row (first *_id column)
    usua_id,          -- acting user from app.usuario_id session setting
    fecha,            -- NOW()
    accion,           -- 'INSERT' | 'UPDATE' | 'DELETE'
    estado_anterior,  -- to_jsonb(OLD)::TEXT  (NULL on INSERT)
    estado_nuevo,     -- to_jsonb(NEW)::TEXT  (NULL on DELETE)
    descripcion       -- 'Registro automático generado por trigger'
)
To attribute a DML operation to a specific user, set the session-level parameter before executing the statement: 
SET LOCAL app.usuario_id = 42;
UPDATE adolescente.expediente SET estado = 'Atraso' WHERE expe_id = 17;
-- The trigger reads current_setting('app.usuario_id') and stores 42 as usua_id
If app.usuario_id is not set (or cannot be cast to INT4), usua_id defaults to 0.

​
seguridad.fn_actualizar_fcmod

Fires BEFORE UPDATE on all tables that have a "fcMod" column. It simply sets NEW."fcMod" = NOW() before the row is written, so application code never needs to manage this timestamp manually. 
CREATE OR REPLACE FUNCTION seguridad.fn_actualizar_fcmod()
RETURNS TRIGGER AS $$
BEGIN
    NEW."fcMod" = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

​
TypeORM Configuration

TypeORM is configured in AppModule via TypeOrmModule.forRoot(): 
TypeOrmModule.forRoot({
  type: 'postgres',
  url: process.env.DATABASE_URL,
  autoLoadEntities: true,
  // The schema is governed by DDL_UZDI_FINAL.sql; TypeORM must not alter it.
  synchronize: false,
}),
OptionValueReason
type'postgres'PostgreSQL driver via pg package
urlprocess.env.DATABASE_URLFull connection string from .env
autoLoadEntitiestrueEntities are registered automatically when their feature module is imported
synchronizefalseSchema is managed exclusively by DDL_UZDI_FINAL.sql
Never set synchronize: true. TypeORM’s schema synchronization does not understand multi-schema PostgreSQL layouts, quoted camelCase column names, or the CHAR(1) boolean convention used in this project. Running synchronize: true will produce a divergent schema that breaks trigger functions and CHECK constraints.

Build docs developers (and LLMs) love

Get started for freeTalk to us