Database Layer: TypeORM Entities and Stored Procedures

Judicial Backend connects to a Microsoft SQL Server database through TypeORM. All entity definitions live in src/database/entities/, and all shared data-access logic — both ORM-based catalog lookups and stored-procedure wrappers — lives in src/database/repositories/. Module-level repositories (in src/modules/<name>/repositories/) use TypeORM’s QueryBuilder for complex queries but never define their own stored-procedure calls; those are always delegated to the shared SP repositories.

DataSource Configuration

The TypeORM DataSource is configured in src/config/data-source.ts. It targets MSSQL, uses glob-based entity auto-loading, and disables schema synchronization (the database schema is managed outside the application).

// src/config/data-source.ts
export const AppDataSource = new DataSource({
    type: 'mssql',
    host: envs.DB_HOST,
    port: envs.DB_PORT,
    username: envs.DB_USER,
    password: envs.DB_PASSWORD,
    database: envs.DB_NAME,
    entities: [path.join(__dirname, '../database/entities/**/*.entity{.ts,.js}')],
    synchronize: false,
    logging: false,
    extra: {
        trustServerCertificate: true,
    },
    options: {
        useUTC: false,
        encrypt: true,
    },
});

The connectDB() helper is called once during server startup (server.ts). If the connection fails, the process exits immediately (process.exit(1)) rather than serving partially-initialized requests. An getManager() export provides the EntityManager that is injected into shared repositories:

export const getManager = () => AppDataSource.manager;

synchronize: false is intentional. The database schema is managed by SQL Server migration scripts; TypeORM never alters tables automatically. This prevents accidental schema changes in production.

TypeORM Entities (21 total)

All entities are in src/database/entities/ and are auto-loaded via the glob pattern above. They are grouped into four categories by naming convention.

Core Entities

These entities model the primary transactional records of the court system.

Entity file	TypeScript class	Description
`apelacion.entity.ts`	`Apelacion`	Root appeal record — folio, dates, Sala assignment, case identifiers
`apelacion-anexo.entity.ts`	`ApelacionAnexo`	Documents and attachments associated with an appeal
`apelacion-historico.entity.ts`	`ApelacionHistorico`	Historical snapshot entity for audit and search
`apelacion-parte.entity.ts`	`ApelacionParte`	Parties (defendants, plaintiffs) involved in an appeal

Catalog Entities

Catalog entities use the catalogo- prefix and back the drop-down data returned by every module’s getCatalogos() endpoint.

Entity file	TypeScript class	Catalog meaning
`catalogo-anexo.entity.ts`	`CatAnexo`	Types of allowed attachments
`catalogo-apelacion.entity.ts`	`CatApelacion`	Appeal categories
`catalogo-delito.entity.ts`	`CatDelito`	Criminal offense definitions
`catalogo-etnia.entity.ts`	`CatEtnia`	Ethnic groups (indigenous case support)
`catalogo-juzgado.entity.ts`	`CatJuzgado`	Originating courts of first instance
`catalogo-localidad.entity.ts`	`CatLocalidad`	Localities, scoped by municipality
`catalogo-magistrado.entity.ts`	`CatMagistrado`	Magistrate roster
`catalogo-materia.entity.ts`	`CatMateria`	Legal subject matters (e.g. criminal, civil)
`catalogo-municipio.entity.ts`	`CatMunicipio`	Municipalities
`catalogo-nomenclatura.entity.ts`	`CatNomenclatura`	Folio nomenclature codes used in toca assignment
`catalogo-sala.entity.ts`	`CatSala`	Court chambers (Salas)
`catalogo-sexo.entity.ts`	`CatSexo`	Sex/gender options

Type Entities

Type entities use the tipo- prefix and represent classification sub-types that depend on a parent catalog entry.

Entity file	TypeScript class	Description
`tipo-apelacion.entity.ts`	`TipoApelacion`	Appeal sub-types, scoped to a parent `CatApelacion` via `idApelacion`
`tipo-escrito.entity.ts`	`TipoEscrito`	Document/writ types
`tipo-parte.entity.ts`	`TipoParte`	Party role types (defendant, victim, etc.)

Relation Entities

These entities model many-to-many or join-table relationships that don’t fit neatly into the core or catalog groups.

Entity file	TypeScript class	Description
`relacion.entity.ts`	`Relacion`	Generic association between appeal records
`delito-relacion.entity.ts`	`DelitoRelacion`	Links criminal offenses to appeal records

Shared Repositories

The four repositories in src/database/repositories/ are shared infrastructure — any module’s service can use them. They fall into two categories: ORM repositories (TypeORM manager.find / manager.findOne) and SP repositories (raw SQL EXEC calls via manager.query).

ORM Repository: `catalogo.orm-repository.ts`

CatalogoOrmRepository provides static methods for every catalog lookup needed across modules. Each method mirrors the behavior of a legacy stored procedure while using TypeORM’s type-safe manager.find API.

// Example: find all active Salas
CatalogoOrmRepository.findSalas(manager);
// → SELECT id, descripcion FROM CatSala WHERE activo = 1 ORDER BY descripcion ASC

Available static methods: findMaterias, findTiposEscrito, findDelitos, findSexos, findTiposParte, findJuzgados, findMagistrados, findApelaciones, findTiposApelacion, findMunicipios, findLocalidades, findEtnias, findAnexos, findSalas, findNomenclatura.

SP Repositories

The three stored-procedure repositories each wrap a specific EXEC call. They accept an EntityManager instance (enabling transaction participation) and the required parameters, and they throw AppError if the procedure returns no rows.

Stored Procedures Reference

Repository file	Class	Stored procedure(s)	Purpose
`folio.sp-repository.ts`	`FolioSpRepository`	`PA_SEL_FolioNomenclaturaToca`	Generate toca folio from Sala and nomenclature codes
`folio.sp-repository.ts`	`FolioSpRepository`	`PA_INS_PCF_FolioTramite`	Insert and retrieve a new procedure folio number
`asignacion-toca-sala.sp-repository.ts`	`AsignacionTocaSalaSpRepository`	`PA_SEL_AsignacionTocaySala`	Determine Sala and Toca assignment for a new appeal
`estadistica.sp-repository.ts`	`EstadisticaSPRepository`	`PA_SEL_PCF_EstadisticaApelaciones`	Retrieve statistical appeal data filtered by Sala, nomenclature, appeal type, and date range

`FolioSpRepository`

Called during appeal registration to obtain two system-generated identifiers:

// Generate folio from nomenclature + sala
const folio = await FolioSpRepository.getFolioNomenclaturaToca(manager, {
    idSala,
    idNomenclatura,
});

// Generate a new procedure (tramite) folio
const folioTramite = await FolioSpRepository.getFolioTramite(manager);

`AsignacionTocaSalaSpRepository`

Determines which Sala and Toca number should be assigned to a new appeal, based on the case origin and appeal type:

const asignacion = await AsignacionTocaSalaSpRepository.getAsignacionTocaSala(manager, {
    idGeneral,
    idAreaSistemaUsuario,
    idPantalla,
    expedienteCausa,
    idApelacion,
    idJuzgado,
    esReposicion,
    idMateria,
});

`EstadisticaSPRepository`

Fetches statistical records for the estadistica module, accepting optional filter parameters — all nullable so the SP can be called with any subset of filters:

const rows = await EstadisticaSPRepository.getEstadisticaApelaciones(manager, {
    idSala,           // optional
    idNomenclatura,   // optional
    idApelacion,      // optional
    fechaInicio,      // optional ISO date string
    fechaFin,         // optional ISO date string
});

Module Repositories vs. Shared Repositories

Understanding the distinction between the two repository layers avoids confusion when reading or extending the codebase:

Characteristic	Module repositories (`modules/<name>/repositories/`)	Shared repositories (`database/repositories/`)
Location	Inside the owning module	Outside all modules
Query style	TypeORM `QueryBuilder` with dynamic filters and JOINs	Raw `EXEC` (SP repos) or `manager.find` (ORM repo)
Reuse	Used only by their module’s service	Used by any module’s service
Transaction	Participate via injected `manager` or `DataSource`	Accept `EntityManager` argument for transaction participation
Examples	`busqueda-apelacion.repository.ts`, `apelacion.repository.ts`	`folio.sp-repository.ts`, `catalogo.orm-repository.ts`

All SP repositories accept a raw EntityManager argument rather than creating their own connection. This means they can participate in the same database transaction as the module-level ORM operations — critical during appeal registration, where the folio SP call and the Apelacion entity insert must succeed or fail together.

Get Started

Architecture

Authentication

Core Concepts

Report Generation

Database Layer: TypeORM Entities and Stored Procedures

DataSource Configuration

TypeORM Entities (21 total)

Core Entities

Catalog Entities

Type Entities

Relation Entities

Shared Repositories

ORM Repository: `catalogo.orm-repository.ts`

SP Repositories

Stored Procedures Reference

`FolioSpRepository`

`AsignacionTocaSalaSpRepository`

`EstadisticaSPRepository`

Module Repositories vs. Shared Repositories

Build docs developers (and LLMs) love

Get Started

Architecture

Authentication

Core Concepts

Report Generation

Documentation Index

​DataSource Configuration

​TypeORM Entities (21 total)

​Core Entities

​Catalog Entities

​Type Entities

​Relation Entities

​Shared Repositories

​ORM Repository: catalogo.orm-repository.ts

​SP Repositories

​Stored Procedures Reference

​FolioSpRepository

​AsignacionTocaSalaSpRepository

​EstadisticaSPRepository

​Module Repositories vs. Shared Repositories

Build docs developers (and LLMs) love

DataSource Configuration

TypeORM Entities (21 total)

Core Entities

Catalog Entities

Type Entities

Relation Entities

Shared Repositories

ORM Repository: `catalogo.orm-repository.ts`

SP Repositories

Stored Procedures Reference

`FolioSpRepository`

`AsignacionTocaSalaSpRepository`

`EstadisticaSPRepository`

Module Repositories vs. Shared Repositories