Confidential Governmental System

01Context

Administrative and document system designed for an isolated institutional network. The core interface and API use local resources. An optional external font in the template is unavailable without internet. Institutional identity, dossiers and topology are withheld.

02Problem

The previous workflow relied on paper binders and separate spreadsheets. Finding a dossier, checking document status and reconstructing movements was slow and vulnerable to divergent copies. Centralization targets those problems. No baseline timing or formal incident study was retained.

03My role

100% individual and autonomous end-to-end design and development of the full system: Express 5 REST API architecture, Prisma data modeling and migrations with embedded SQLite, interactive local-first SPA UI with self-hosted vector icons, HMAC-SHA256 cryptographic audit engine, AES-256-GCM encrypted backup pipeline, and Windows deployment automation via background Windows Service and client BAT scripts. No third-party delegation was involved in software development. Institutional identity, personnel information, and source code remain reserved.

04Constraints

The intended setting is an isolated institutional network with Windows workstations. Personnel data, real screenshots, reserved code, credentials, addresses and infrastructure details cannot be published. Confidentiality also limits public verification of deployment. Offline use requires local assets; an optional external font may be absent.

05Solution

The dashboard brings together sections, people, checklist status, documents and movements. Search normalizes text and filters data already loaded in the browser. Administrators can modify and export; read-only users access permitted views. Files live separately and require authentication for delivery. The system records events for later inspection.

06Architecture

Express 5 serves HTML/CSS/JS and the API. Prisma stores records in SQLite; documents live on disk with references in the database. Routes apply HTTP controls, identity and role before business operations. HMAC-SHA256 audit writes are attempted after relevant changes. A scheduled process backs up the database and files; AES-256-GCM encryption depends on a valid key. HTTPS depends on configured certificates. The diagram shows alternative data branches and a separate backup task.

07Critical flow and pipeline

1. A user signs in; the server validates credentials and issues a revocable JWT.

2. Protected Express routes check session and role. Mutations with defined bodies use Zod validation.

3. Staff read or update people, checklists and movements. Prisma persists records in SQLite; file uploads add binary type checks and disk storage. The interface displays and filters the response.

4. After a relevant operation, the server attempts to append an audit event. That write has its own transaction: an audit failure does not automatically roll back the main change.

5. Separately, scheduled backup collects database and files. Restore runs and is checked through another command.

08Concurrency and data integrity

The audit queue serializes writes within one process. In the isolated copy, 25 concurrent promises produced 25 linked events and a valid chain. Multiple processes and sustained load were not tested. Administrative writes and audit writes are not atomic together. Access control uses shared roles, not ownership of an individual dossier; isolation between users with the same role is not claimed. Recovery requires matching database and files.

09Engineering decisions

SQLite with Prisma keeps persistence on the local server without another database service; there is no evidence of scaling or explicitly configured WAL. Separating files from records enables controlled downloads and backups, but both must be restored together. HMAC chaining detects changes to retained events without making them immutable. Windows scripts automate repeatable steps while operators make installation choices. These reasons are reconstructed from code and documented constraints; no formal ADR dates each decision.

10Security and defense in depth

Revocable JWTs, admin/viewer roles, Zod for defined bodies, request limits and Helmet headers are implemented. Files require a session. X-Requested-With is a request header gate, not a CSRF token. HMAC-SHA256 detects changes to retained events with trusted keys; it does not prove all operations were logged or prevent deletion. Backups are encrypted only with a valid key. An isolated network reduces external exposure but does not replace endpoint or operational controls.

11Testing and scope

The source code was copied into a separate temporary folder and installed from its lockfile. Tests used temporary SQLite, fictional users and loopback. All 17 original tests passed in two files, covering sessions, roles, CRUD, persistence, audit export, upload/download, validators and utilities. Ten additional checks passed against real modules: 25 concurrent audit writes in one process, tamper and deletion detection, key rotation and sanitization. Six BAT simulations and message/filter checks passed. Prisma validation passed with a synthetic database URL; the initial invocation without one failed on missing configuration. npm audit --omit=dev reported four advisories and a nonclean exit. Deployment and the institutional service were not run.

12Deployment and operation

The repository prepares the server with a PowerShell assistant and clients with BAT. The operator chooses installation and certificate settings; HTTPS depends on configuration. Scheduled backups are encrypted only with a valid key; restore verification is separate. I developed, tested, and operated the software locally on an isolated institutional network. This technical review validated an isolated copy and did not access the active installation.

13Outcome

The system implements searchable digital dossiers, document status, movements and retained events with checkable integrity. I estimate about 60% less processing and retrieval time than the former manual workflow. This is my estimate: no baseline, period, sample or calculation method was retained to audit it. This review did not measure use or performance in the institutional installation.

14Evidence and limits

Implemented: routes, schema, interface, audit, backups and scripts in the reviewed source. Locally checked: original suite, additional harness, syntax, filters and schema, with fictional data. Simulated: client BAT setup. Development and deployment: sole full-stack development, prior institutional deployment and the 60% estimate. External pending: current service status, contemporary restore, sustained load and dependency advisories. The private repository’s public GitHub page was inaccessible. No dossiers or reserved source are published.

15Software quality and UX

The interface groups records by section and supports name or grade search with accent normalization and multiple terms. Local tools checked status filters and error messages; no usability session with operators was run. The following sample explains search over loaded rows and tests fictional data. It does not represent authorization or reveal security logic.

16Code sample and engineering test

Isolated public software quality and UX sample: multi-term search ignoring accents and case over rows already loaded. It summarizes visible client behavior; it is not the literal institutional module and does not implement permissions.

1
// Isolated UX example: filter rows already loaded in the browser.
2
export function findPeople(rows, query) {
3
  const normalize = (value) => String(value ?? '')
4
    .normalize('NFD')
5
    .replace(/[\u0300-\u036f]/g, '')
6
    .replace(/[^\p{L}\p{N}\s]/gu, '')
7
    .toLowerCase()
8
    .trim();
9
  const terms = normalize(query).split(/\s+/).filter(Boolean);
10
  if (terms.length === 0) return rows;
11
  return rows.filter(({ grade, name }) => {
12
    const visibleText = normalize(`${grade} ${name}`);
13
    return terms.every((term) => visibleText.includes(term));
14
  });
15
}

1
import assert from 'node:assert/strict';
2
3
const rows = [
4
  { grade: 'Inspector', name: 'Ana Pérez' },
5
  { grade: 'Analista', name: 'Mario Soto' },
6
];
7
assert.deepEqual(findPeople(rows, 'PEREZ inspector'), [rows[0]]);
8
assert.deepEqual(findPeople(rows, 'ana inexistente'), []);
9
assert.deepEqual(findPeople(rows, '  '), rows);
10
console.log('3/3 synthetic search cases passed');

The exact displayed code and test were executed: 3/3 synthetic cases passed (accent and term order, no matches, empty query). No institutional records were used.