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 users. An earlier attempt at a cloud rebuild existed but had never gained traction with users.

The business needed to:

• Replace the aging desktop application with a modern, cloud-native SaaS platform without disrupting the existing user base
• Preserve decades of accumulated pharmaceutical data -- drug references, dosage formulas, interaction rules, client info sheets -- with zero data loss
• Open up a B2B API distribution channel so that partners (diagnostic platforms, practice management systems, etc.) could embed the reference data into their own products
• Introduce data governance and auditability suitable for a clinical decision support product
• Support a gradual transition -- legacy desktop users and new cloud users must continue to operate on a single authoritative data set during the migration window

---

What We Built

We are building the new platform from the ground up on an enterprise-grade stack, migrating all critical data from the legacy Access database, and delivering a production-ready system with a B2B API layer, an administrative panel, a reference portal, and a self-service partner portal.

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 the admin panel, reference portal, and B2B partner portal
• JWT authentication for internal users and API key authentication for B2B partners, with rate limiting and request quotas

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, and a 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 info 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 for partner stability

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 -- critical for a clinical reference product where data changes 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. This lets both cloud and legacy users operate on the same authoritative dataset throughout the transition.

DevOps

• Dockerfiles and docker-compose for local parity
• GitHub Actions CI/CD with staging/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

---

Technical Highlights

• Schema reconciliation -- translating a decades-old denormalized Access database into a clean relational PostgreSQL model while preserving every clinical relationship
• Business logic archaeology -- reverse-engineering dosage and interaction logic from legacy data + prior cloud code, then validating outputs match the legacy system exactly
• Two-way data compatibility -- cloud is the system of record, but legacy users continue to receive drug data updates in their native format
• Partner-ready API layer -- documented APIs built to integration-readiness so partner onboarding is a configuration exercise, not an engineering one
• Clinical-grade governance -- draft/approve, temporal audit, and rollback baked into the data model from day one

---

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
Legacy Source	Microsoft Access / Visual Basic

---

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

---

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
• Production-ready B2B API layer with documented endpoints, rate limiting, webhooks, and self-service partner onboarding
• Clinical-grade data governance -- every drug data change is drafted, reviewed, versioned, and reversible
• Smooth transition path: legacy desktop users continue to receive authoritative data updates from the cloud admin in their native format until they migrate

---

Key Takeaway

Replacing a mature, domain-heavy legacy application is not just a technology rewrite -- it is a migration of institutional knowledge. The hardest parts of this engagement were not building APIs or wiring up Stripe; they were reconstructing clinical business logic from a decades-old Access database, reconciling it with an abandoned cloud attempt, and proving that the new system produced identical results to the one veterinarians already trusted. By combining careful schema reconciliation, draft-approve governance, cloud-to-legacy data compatibility, and a partner-ready API layer, we are giving the client both a modern SaaS product and a distribution channel it never had before -- without asking its existing user base to take a leap of faith.