Veterinary Pharmaceutical Reference Platform — Legacy to Cloud SaaS

Domain: Healthcare / Veterinary Pharmacy / B2B SaaS Engagement: Fixed Fee with T&M overage provision Duration: ~11 months (16 weeks active development + 7 months production support) Team Size: Architect, Project Manager, Senior & Junior Full-Stack Engineers Client Location: United States

---

The Problem

The client operates a long-standing pharmaceutical reference product used by veterinary medicine professionals to support safe and effective prescribing. The existing platform was a legacy Microsoft Access / Visual Basic desktop application with roughly 600 active clinical users. An earlier attempt at a cloud rebuild existed but had never gained traction with users — a common failure mode when domain knowledge lives implicitly in legacy code and doesn't survive translation.

The business needed to:

• Replace the aging desktop application with a modern, cloud-native SaaS platform — without asking 600 clinical users to take a leap of faith
• Preserve decades of accumulated pharmaceutical data (drug references, dosage formulas, interaction rules, client information sheets) with zero data loss
• Open up a B2B API distribution channel so partners (diagnostic platforms, practice management systems) could embed the reference data into their own products — a revenue line the legacy desktop simply couldn't support
• Introduce data governance and auditability suitable for a clinical decision support product, where every change has downstream clinical consequences
• Support a gradual transition: legacy desktop users and new cloud users had to operate on a single authoritative data set during the migration window, without the cloud platform forking the truth

The project was structured as a rebuild, but the real engineering problem was a migration of institutional knowledge — and the failed prior cloud attempt was a visible reminder of what happens when that's underestimated.

---

What We Built

A ground-up cloud platform on an enterprise-grade stack, migrating all critical data from the legacy Access database, with a B2B API layer, an administrative panel, a reference portal, and a self-service partner portal — plus a sync channel back to the legacy desktop client to support users who aren't ready to migrate.

Ground-up cloud platform

• NestJS API layer with modular architecture, guards, interceptors, and structured error handling
• PostgreSQL relational database with enforced referential integrity, replacing the flat Access table structure
• Next.js + Tailwind for admin panel, reference portal, and B2B partner portal
• JWT for internal users, API keys for B2B partners, with rate limiting and request quotas enforced at the API edge

Data migration from legacy desktop

• Field-level mapping of legacy Access tables into a fully normalized PostgreSQL schema
• Migration scripts for core drug data, embedded data (brand names, adverse effects, dosages, client info sheets), junction tables, and drug-to-drug interaction data
• Data integrity validation: source vs target record counts, foreign-key constraint verification, validation log per run
• Business logic preservation — dosage calculations and drug interaction rules reverse-engineered from legacy data and prior cloud attempts, then verified to produce identical outputs to the legacy system

B2B API platform

• RESTful APIs for drug reference, dosage calculator, interaction checker, and client information sheet generation
• Webhook system with event dispatch, partner registration, retry logic, and delivery logging
• Comprehensive OpenAPI / Swagger documentation with working examples
• Versioning and error-handling middleware designed for partner stability over time, not just at launch

Admin panel

• Full CRUD for drugs, interaction rules, dosage formulas, categories, and body systems
• Partner + API key management with usage statistics
• Webhook configuration with delivery status visibility
• Excel/CSV bulk import tool with validation for ongoing drug data updates

Reference & partner portals

• Reference portal — read-only drug lookup, dosage calculator, and interaction checker demos, used for sales enablement and partner evaluation
• B2B partner portal — self-service registration with email verification, onboarding wizard, API key self-service, webhook configuration, interactive API sandbox, and usage analytics dashboard

Subscription & billing

• Stripe-integrated subscription flow for individual UI users with plan/pricing model
• Admin-managed billing mode for API partners
• Usage metering and programmatic limit enforcement
• Partner billing dashboard and admin billing view

Data versioning & governance

• Temporal audit tables with triggers capturing every change to clinical data
• Draft → Approve workflow so no change reaches production users until reviewed
• Version history UI with diff viewer and rollback capability
• Audit log dashboard and bulk-operation versioning — essential for a clinical reference product where every change must be traceable

Cloud-to-legacy sync

To support users who are not yet ready to migrate off the desktop app, the cloud admin can export updated drug and clinical data in a format compatible with the legacy desktop application (.mdb / .csv). Legacy users receive the package via email or download link and import it into their desktop client. Cloud and legacy users operate on the same authoritative dataset throughout the transition — the cloud is the single source of truth even for users who haven't switched to it.

DevOps

• Dockerfiles and docker-compose for local parity with production
• GitHub Actions CI/CD with staging and pre-production pipelines
• AWS infrastructure (ECS/EC2, RDS PostgreSQL, S3, CloudFront, SES, Route 53, ACM, CloudWatch)
• Monitoring, structured logging, and automated backup / disaster-recovery configuration

---

Architecture Deep Dives

Business logic archaeology — and how we proved correctness

The hardest problem on this engagement wasn't building the APIs — it was ensuring that dosage calculations and drug interaction rules in the new system produced identical results to the ones clinicians had relied on for years. A near-miss would be worse than a total failure: silent output drift in a clinical reference product is exactly the kind of thing that erodes trust irreversibly.

The approach:

• Extract the logic from every available source — Access forms, VBA modules, prior cloud attempts, embedded data tables, and undocumented historical behavior preserved in the data itself
• Rebuild in TypeScript as pure, testable functions with explicit contracts, not scattered through controllers
• Build a parallel-run harness — run every dosage formula and interaction rule against a cohort of real historical inputs in both the legacy system and the new implementation, then diff outputs byte-for-byte
• Treat every diff as a ticket — either the legacy system has a bug the client wants corrected (document the deviation), or the new implementation is wrong (fix it). Nothing was ever "close enough"
• Lock in results with golden-file tests — the diff harness became a regression suite that any future refactor has to pass

This is slower than "just rewrite it" but it is the only approach that produces a system a clinician will trust.

Schema reconciliation — relational-first, intentionally

Access databases accumulate a particular kind of pathology: duplicated data across tables, implicit relationships that exist only in the UI code, columns whose meaning changed over time. Translating that into a clean relational PostgreSQL schema required deciding — for every table — what was essential relationship, what was denormalization for editing ergonomics, and what was accidental. Enforced foreign keys, check constraints, and properly-typed columns replaced a decade of "please enter a valid date" comments on a text field. The schema is the first place where the migration made the data better than it was, not just portable.

Draft → approve workflow + temporal audit

Clinical data changes don't just need an audit log — they need time-travel. Historical prescribing decisions that referenced a drug record need to be interpretable against the version of that record they saw, not whatever it looks like today. The governance model is:

• All clinical entities have a current version and a history via temporal audit tables populated by triggers
• Changes land in a draft state and are invisible to end users until explicitly approved by an authorized reviewer
• The version history UI provides per-field diffs and a one-click rollback; rollbacks themselves are versioned events
• Bulk imports go through the same draft/approve gate — a spreadsheet paste cannot silently change clinical data

API versioning and partner stability

B2B partners ship products that embed this data. Breaking their integrations breaks their product — which breaks our trust with them. API versioning is explicit (URL-scoped), deprecation windows are communicated in both the response headers and the partner portal, and breaking changes are never shipped inside a minor version. The API's public contract is a first-class artifact — not whatever the code currently happens to do.

Webhook reliability

Webhook delivery to partners is a system that will fail — their endpoints will go down, their TLS certs will expire, their IPs will change. The delivery pipeline is built around that reality:

• Exponential-backoff retries with a bounded retry horizon
• Per-partner delivery status visible in the admin panel and the partner's own portal
• Signed payloads so partners can verify authenticity
• A dead-letter queue for terminal failures with explicit replay tooling
• Delivery logs retained long enough to support partner support conversations weeks after an incident

Cloud as system of record, legacy as subscriber

The two-way compatibility story is frequently done wrong — teams allow legacy clients to continue writing and then spend the project fighting sync conflicts. We designed from day one with the cloud as the single system of record; legacy is strictly a read-only subscriber consuming periodic data packages. There is one authoritative dataset, versioned and audited; legacy users get slightly older snapshots of it until they migrate.

---

Delivery Approach

The engagement runs as a fixed-fee project with a production-support tail:

• Phase 1–2 (Wk 1–5): Discovery, reverse-engineering of legacy data and business logic, architecture design, PostgreSQL schema design, API contract definition
• Phase 3–4 (Wk 3–8): NestJS API development (drug reference, dosage, interactions, client info sheets, webhooks), data migration, admin panel
• Phase 5–8 (Wk 5–11): Reference portal, B2B partner portal, Stripe subscription & billing, data versioning & governance
• Phase 9–10 (Wk 10–13): DevOps hardening, end-to-end testing, performance validation
• Phase 11–12 (Wk 12–14): Pre-production deployment, data cutover rehearsal, UAT with client stakeholders
• Phase 13 (Wk 13–16): Go-live, post-launch monitoring, stabilization, partner onboarding support
• Phase 14 (+6 months): Continued production support — bug fixes, performance tuning, security patches, partner onboarding

---

Tech Stack

Layer	Technology
Backend / API	NestJS (Node.js), TypeScript
Database	PostgreSQL (relational, with temporal audit tables)
Frontend	Next.js, React, Tailwind CSS
Authentication	JWT (admin/UI), API keys (B2B partners)
Billing	Stripe (subscriptions + webhooks)
Email	AWS SES
Infrastructure	AWS (ECS/EC2, RDS, S3, CloudFront, Route 53, ACM, CloudWatch)
DevOps	Docker, GitHub Actions, staging + production environments
API Documentation	OpenAPI / Swagger
Validation	Parallel-run harness + golden-file regression suite against legacy outputs
Legacy Source	Microsoft Access / Visual Basic

---

Outcome

• Replacement cloud SaaS platform delivered for a legacy desktop product with ~600 active clinical users
• All core drug, dosage, interaction, and client-info data migrated with zero data loss and validated record-for-record against the legacy source
• Dosage and interaction logic validated to produce identical outputs to the legacy system via a parallel-run diff harness — the single highest-stakes correctness requirement of the engagement
• Production-ready B2B API layer with documented endpoints, versioning discipline, rate limiting, signed webhooks, and self-service partner onboarding — a distribution channel the legacy product never had
• Clinical-grade data governance: every drug data change is drafted, reviewed, versioned, and reversible; historical states remain queryable
• Smooth transition path: legacy desktop users continue to receive authoritative data updates from the cloud admin in their native format, with the cloud as the single source of truth

---

Key Takeaway

Replacing a mature, domain-heavy legacy application is not a rewrite project — it is a migration of institutional knowledge, and the failure mode most rebuilds run into is underestimating that. The hardest parts of this engagement were not the APIs or Stripe integration; they were extracting dosage and interaction logic from a decades-old Access database and an abandoned cloud attempt, proving the new system produced byte-identical results to the one veterinarians already trusted, and designing governance that keeps clinical data auditable going forward. The combination of schema reconciliation, parallel-run validation, draft-approve governance, cloud-to-legacy sync, and a partner-ready API layer gave the client both a modern SaaS product and a new distribution channel — without asking 600 clinical users to take a leap of faith.