>_ Jorge Uc / projects← All projects
~/projects/clinical-lab // DATA AUTOMATION
◆ Real-world engineeringOffline-firstPublic demo

Building for reality, not ideal conditions.

Clinical Laboratory Management System began as a solution to an operational challenge: manage clients, services, sales and reports across laboratory branches where internet access could disappear for hours or days. The result connected Python, SQLite and Excel automation in a desktop-first workflow.

The biggest outcome was not a model or an algorithm. It was learning how to design useful data systems around the people, constraints and tools of a real organization.

Sanitized desktop application dashboard with synthetic demonstration records
DESKTOP APPLICATION / SYNTHETIC DATA
APPLICATIONPythonTkinter desktop UI
STORAGESQLiteLocal structured records
INTEROPERABILITYExcelImport / export workflows
APPROACHOfflineConnectivity-aware design
01 / PROJECT CONTEXT

The environment shaped the architecture.

A business application is successful when it fits its users' actual workflow. Here, unreliable connectivity and mandatory spreadsheet formats were requirements, not edge cases.

01INFRASTRUCTURE

Internet could not be trusted

Some branches experienced outages lasting hours or even days. Daily data entry could not depend on a continuously available cloud service.

CONSTRAINT → LOCAL-FIRST STORAGE
02WORKFLOW

Excel was non-negotiable

Laboratory staff needed documents with specific information and layouts. Replacing spreadsheets altogether would have disrupted existing work.

CONSTRAINT → EXCEL INTEROPERABILITY
03DATA MANAGEMENT

Records needed structure

Clients, studies, sales, payment information and historical activity needed to be organized so records could be retrieved and summarized consistently.

CONSTRAINT → SQL + AUTOMATION
02 / SYSTEM ARCHITECTURE

Local by design. Structured by default.

The diagram is a conceptual overview of the workflow, not a disclosure of private infrastructure or exact production schemas.

> data.pipelineSIMPLIFIED / CONCEPTUAL
01 / INPUTTkinter desktop UIClient, study, sales and payment records
02 / PERSISTENCESQLiteLocal records and SQL queries
03 / PROCESSINGPython + PandasFiltering, cleaning and aggregations
OPERATIONSLaboratory receipts

Excel files with patient, study and collection-related details

Local use does not imply automatic synchronization between branches. Transfer and backup workflows must be managed separately.

03 / APPLICATION CAPABILITIES

One workspace for everyday operations.

The original system supported several administrative processes. The public demo reproduces their essential concepts using fictitious data and a simplified interface.

01 / CLIENTS

Customer records

Store and retrieve client information for ongoing transactions and historical consultation.

02 / CATALOG

Studies and pricing

Maintain a searchable catalog of laboratory studies, prices and associated information.

03 / SALES

Transaction management

Register selected services, quantities, transaction totals, identifiers and payment information.

04 / SEARCH

Historical queries

Explore registered operations and use filters such as date, customer, service or branch.

05 / DOCUMENTS

Excel generation

Transform recorded information into operational spreadsheets and economic reports.

06 / DATA EXCHANGE

Import, export & backups

Exchange structured information with Excel and support local data-management routines.

APPLICATION SCREENSHOTS / PUBLIC DEMO
Customer management — public demo using synthetic records
Customer managementCreate, search and update fictional customer records.
Sales registration — public demo using synthetic records
Sales registrationRecord services, amounts and payments in the local application.

Screenshots should show only synthetic data from the public demonstration.

04 / AUTOMATED EXCEL WORKFLOWS

Two exports. Two different audiences.

Excel was a business requirement. The important engineering task was not merely writing an .xlsx file, but producing the right information for the right team.

XLLABORATORY WORKFLOW

Operational receipts

Used by laboratory staff to access the information associated with selected patient transactions during a chosen date range.

  • Patient and physician details
  • Requested studies and quantities
  • Collection instructions and notes
  • Dates, identifiers and delivery information
  • Amounts paid and remaining balances
ΣADMINISTRATIVE WORKFLOW

Economic reports

Designed for reviewing the financial activity represented by recorded transactions over a selected period.

  • Sales history and transaction totals
  • Counts and summaries by category
  • Period and branch filters
  • Recorded costs where available
  • Profit indicators only when cost data supports them
EXCEL OUTPUTS / SYNTHETIC DATA
Operational Excel receipts — public demo using synthetic records
Operational Excel receiptsFormatted records designed for laboratory staff.
Economic Excel report — public demo using synthetic records
Economic Excel reportStructured sales and financial summaries for administration.

Both Excel screenshots must use fictitious patient and business information.

AUTOMATION PATH
Record→Query→Transform→Format→Export

A single set of structured records can produce different outputs without duplicating the original data-entry process.

05 / DATA ENGINEERING & ANALYTICS

From transactions to usable information.

The data-related skills in this project are not predictive modeling. They are the steps that make business information consistent, queryable and reportable.

01 SQLITE / SQL

Local data persistence

Structured tables and queries support transactional records and history without requiring a network connection for local operations.

CAPTURE · RETRIEVE · FILTER
02 PYTHON / PANDAS

Data transformation

Prepare tabular records for summaries, clean data types and aggregate information for administrative reporting.

TRANSFORM · GROUP · SUMMARIZE
03 EXCEL LIBRARIES

Interoperable deliverables

Produce spreadsheets that match established laboratory and business workflows rather than forcing a new file format.

EXPORT · FORMAT · REUSE
Analytics scope. This is a process automation and data management case study, not a machine learning project. No fabricated gains in productivity, accuracy or revenue are claimed.
06 / PUBLIC DEMONSTRATION

Explore the ideas, not the private data.

The linked repository is intended to showcase a separately implemented, privacy-conscious demonstration of the project's core workflows.

OPEN SOURCE VIEW / SANITIZED REIMPLEMENTATION

Same engineering concepts.
Fictitious information.

The public version uses demo records, generic visual elements and local SQLite storage. It does not contain actual client data, internal business documents, production credentials or original company branding.

Explore the repository ↗
● ● ● demo / terminal
$ python scripts/init_demo.py
› Synthetic dataset initialized

$ python main.py
› Local desktop application

# No production data required

The commands illustrate the demonstration workflow; see the repository README for current setup instructions. The conceptual preview above is not a screenshot of either application.

07 / PERSONAL RETROSPECTIVE

What I learned building this system.

This project changed how I think about data work: solving the right operational problem often matters more than choosing the most sophisticated technology.

BEYOND THE CODE

Building an offline-first tool for real operational workflows taught me that good software must respect infrastructure limits, data quality, existing processes and the people who use the outputs.

01 / SYSTEM DESIGN

Architecture is a response to constraints

I learned that a cloud-based architecture is not automatically a better one. Because some branches could lose internet access for extended periods, I favored local persistence with SQLite. This made the daily workflow less dependent on connectivity, while requiring careful thinking about backups and moving information between locations.

Offline-firstSQLiteTrade-offs
02 / DATA ENGINEERING

Structured data reduces repetitive work

Recording customer, study and transaction information in queryable tables allowed the application to retrieve historical data, filter operations and produce outputs without repeatedly retyping the same information. I became more aware of the importance of consistent formats and data relationships.

SQLData modelingData quality
03 / AUTOMATION

Integration beats forcing a new workflow

Laboratory staff specifically needed Excel documents. Rather than treating that as a limitation to eliminate, I learned to integrate the format into the workflow: query records in Python, transform them into the required structure and generate ready-to-use spreadsheets.

PythonPandasopenpyxl
04 / ANALYTICAL THINKING

Operational records can become insights

Once transactions were recorded consistently, the same information could support date-range filtering, aggregate summaries and economic reports. I learned to distinguish raw data, revenue, costs and profit—and to be explicit about how each metric is calculated.

ReportingAggregationBusiness metrics
05 / PRODUCT DEVELOPMENT

Users define what “useful” means

The needs of laboratory technicians and administrative staff were different. One group needed patient-oriented operational receipts; another needed financial summaries. Building both reinforced that usability starts with understanding who will consume each output.

RequirementsUXStakeholders
06 / RESPONSIBLE ENGINEERING

Privacy is part of the design

Working with sensitive records taught me that useful software also needs careful access, data handling and responsible disclosure. The public portfolio version is intentionally a separate, sanitized demonstration; real records, credentials and company-specific visuals are not included.

PrivacySecurity awarenessEthics
KEY TAKEAWAY

Before data can be analyzed, it must be captured, organized and trusted.

This experience strengthened the foundation behind analytics: data collection, persistence, transformation, validation, reporting and communication with the people who use the results.

08 / RESPONSIBLE DISCLOSURE

Real professional experience. Private operational details.

The portfolio explains the engineering context without identifying patients, exposing private operational data or reproducing proprietary visual assets.

SHARED PUBLICLY

Design decisions and technical concepts

General business requirements, local-first architecture, data workflow, Excel automation, technical learnings and a synthetic-data demonstration.

NOT SHARED

Real-world sensitive material

Actual patient and financial records, physician identities, business contact details, credentials, original artwork and private branch-specific information.

Expanded screenshot