Skip to content

Case File, Procedure and Internal Memo Management Platform

An administrative workflow that lived on paper and in people's memory, turned into an explicit workflow: a state machine, its own authorization layer and configuration for what actually changed. Thousands of case files, more than 10 departments and around 60 people, on PHP 7 and SQL Server, without being able to add a single external library.

The Entre Ríos Provincial Education Council (Consejo General de Educación) is the governing body of the education system in the province of Entre Ríos, Argentina. The system is internal to the agency and covers virtually every case file the Council receives that is tied to payroll processes.

  • PHP 7
  • Apache
  • SQL Server
  • PDO/ODBC
  • JavaScript
  • jQuery
  • AJAX
  • Bootstrap
The path of a case file
  1. Upload
  2. Sent
  3. Received
  4. In progress
  5. On hold / Closed

Reopened: back to upload, keeping its history

The problem

Before digitization, the workflow depended on paper and on the informal knowledge of the people involved in each process. Finding out where a case file was, who had it and what had happened to it meant asking around.

Exceptions and special cases weren't written down anywhere: they lived in the heads of the people who had seen them before. When that person wasn't around, the case file stalled.

Case files could end up in wrong or stuck states, and keeping track of responsibilities across departments was hard.

Rules, contracts and permissions changed often, so a rigid workflow broke as soon as real operations changed.

Constraints

  • There was no freedom to add frameworks, libraries or external services. Anything that didn't come with PHP, SQL or JavaScript had to be written.
  • Constraints of the agency's own infrastructure and limited resources.
  • The work was on legacy technology and existing code, not a greenfield project.
  • SAGE, the province's school management system, already had its own scheme of general roles and permissions, but it wasn't enough to represent the rules of the payroll module.

Decisions

  1. An explicit state machine instead of independent flags

    The current state determines which stage and which department the case file is in, and which operations can be performed on it. Several loose boolean flags allow combinations that don't exist in the real workflow, and those are exactly the combinations that leave case files stuck.

    Trade-off: More upfront complexity: every transition and every exception has to be modeled carefully before writing code. In exchange, the workflow stays consistent and under control.

  2. A role and permission system of the module's own, separate from SAGE's

    SAGE's general permissions didn't capture what the payroll workflow needed: that certain owners and managers could review or change other users' decisions, while everyone else had a narrower scope. Since roles changed often, they're managed from a dashboard rather than by editing the database by hand.

    Trade-off: A second authorization layer to maintain and keep consistent with the first. The benefit is independence from SAGE and being able to adapt the module's permissions at the pace they actually change.

  3. Make configurable what changes, keep in code what doesn't

    Departments, responsibilities and permissions changed with operations, and each change couldn't mean a deployment. That's what the configuration targets: departments, roles and permissions, and workflow behavior.

    Trade-off: More upfront effort and more validation to write. The risk at the other extreme — making everything configurable — is ending up with dynamic logic that's hard to read and maintain, so the line was drawn at what actually changed.

Architecture

The case file is the central entity and its state drives the workflow: which stage it's in, which department, and which operations it allows. On top of that run two authorization layers — SAGE's general one and the payroll module's own, managed from a dashboard — and a configuration layer for departments, roles and workflow behavior. PHP 7 backend on Apache, SQL Server access through PDO/ODBC, and a JavaScript frontend with jQuery and AJAX on Bootstrap.

Outcome

  • The workflow stopped being paper and informal knowledge and became an explicit flow, with states, owners, permissions and tracking.
  • It covers virtually every payroll-related case file the Council receives: thousands of case files, more than 10 departments and around 60 people with different responsibilities within the workflow.
  • A good part of the work wasn't adding features but diagnosing and fixing what was already broken: states that got stuck, workflow inconsistencies, duplicates from badly built JOINs, N+1 problems and expensive queries.

What I'd do differently today

  • I'd keep the two core decisions: the state machine and the separation between general authorization and domain-specific permissions. They fit the problem well. What I'd change is the implementation.
  • A more modular architecture, with workflow, authorization, persistence and presentation separated, and a more explicit domain model.
  • Automated tests on transitions and authorization. Those are the two areas where an error stays invisible until a case file ends up where it shouldn't.
  • Explicit concurrency and transaction control. The system had real concurrency problems and that's the answer that was missing.
  • Structured auditing of every transition: who, when, previous state and new state. Not a generic movement log.
  • Observability from day one, to catch slow queries, workflow errors and degradations without waiting for someone to report them.
  • A clearer line between the rules that are configurable and the ones that belong in code: the goal is to keep the flexibility without turning the system into dynamic logic that's hard to maintain.
Let's talk about this project