VetSync

A veterinary clinic group was running appointments, patient history, and staff coordination across spreadsheets, paper charts, and phone calls. Nothing reconciled. A pet's vaccination history lived in one place, the appointment that produced it in another, and neither was visible to the front desk when an owner called.

The system had to hold four groups of people with genuinely different needs in one place: pet owners who book and track their pets, clinic administrators who run the schedule and staff, veterinary professionals who see patients and write records, and a system administrator overseeing clinic approvals across the platform.

The interesting constraint was that clinics do not run on fixed slots. Appointments get moved, walk-ins take priority, and the front desk makes judgment calls. That shaped the scheduling design more than anything technical.

Appointments are requests, not reservations. This is the single decision that shapes the rest of the system, so it is worth stating plainly.

A pet owner picks a clinic, a pet, a service, and a preferred date and time. The frontend offers 30-minute times derived from that clinic's opening hours for the chosen weekday, and the appointment is written with status pending. Nothing is reserved at that moment.

A clinic administrator then reviews the queue, approves the request, and assigns a veterinary professional to it. The approval is where the clinic's own judgment gets applied, and it is also the only point where a vet is attached to an appointment. The owner gets an email when the request is approved or rejected.

After the consultation, the assigned vet writes an electronic health record against the appointment. Vaccinations, dewormings, prescriptions, and lab results are separate models rather than free-text fields, so a pet's history can be queried per record type instead of parsed out of notes.

Approval queue instead of slot locking

A booking system can either reserve a slot at request time or collect requests and let a human resolve them. I went with the second, because a clinic's real constraints (a vet running late, an emergency walk-in, a procedure that takes longer than booked) are not expressible in a slot table, and the front desk resolves them every day anyway.

The cost: two owners can request the same time and both requests will be accepted. There is no unique constraint on clinic, date, and time, and no availability check in createAppointment. That is deliberate, but it does mean the clinic admin queue is load-bearing. If clinic volume grew to the point where the admin was arbitrating dozens of collisions a day, this would need to become a real reservation system with a partial unique index on approved appointments and a transaction around the approval.

Authentication in middleware, authorization in handlers

Tokens are issued as httpOnly cookies with a separate refresh token, so the access token is never readable from JavaScript. A single authenticate middleware verifies it and loads the full user onto the request.

Authorization is deliberately not in that middleware. Ownership rules here are relational rather than role-flat: a clinic admin may only assign a vet who belongs to their own clinic, which is a database question, not a role check.

const vetProExists = await VetProfessional.findOne({
  where: {
    user_id: vetProId,
    clinic_admin_id: clinicAdminId,
  },
});
 
if (!vetProExists) {
  throw new Error("Vet Professional does not belong to this Clinic");
}

The cost: every new endpoint has to remember its own check, and there is no single place to audit who can do what. A requireRole middleware for the coarse cases, with relational checks left in the handlers, would give most of the safety back without flattening the model. That is the first thing I would change.

One users table with role-specific satellites

Shared identity fields (email, password hash, name, phone) live on users with a user_type enum. Pet owners, clinic admins, and vet professionals each get their own table keyed on that user id, holding only what their role actually needs.

The cost: almost every meaningful read is a join, and the role tables are asymmetric. System admins have no satellite table at all, they are just a user_type value, which is a small inconsistency that shows up whenever code branches on role.

Activity logging as its own concern

An audit middleware records method, path, status code, actor, IP, and response time on every finished request, without any controller having to opt in. The platform-wide admin dashboards are built from this rather than from operational tables, so reporting never puts load on or couples itself to the appointment and EHR schemas.

export const auditLogger = (req, res, next) => {
  if (req.method === "OPTIONS") return next();
 
  const start = Date.now();
 
  res.on("finish", async () => {
    if (res.statusCode >= 400 && res.statusCode < 500 && !req.user) return;
 
    try {
      await AuditLog.create({
        method: req.method,
        url: req.originalUrl,
        status_code: res.statusCode,
        user_id: req.user?.id || null,
        user_email: req.user?.email || null,
        user_type: req.user?.user_type || null,
        ip_address: req.ip || req.connection?.remoteAddress || null,
        response_time_ms: Date.now() - start,
      });
    } catch (err) {
      console.error("Audit log write failed:", err.message);
    }
  });
 
  next();
};

The write is fire-and-forget on the finish event, so a logging failure degrades reporting but never fails the user's request. It also writes one row per request, which is fine at clinic scale and would need sampling or a queue well before it became a problem.

Google Drive for uploaded images

Pet photos and profile images go to Google Drive through a service account rather than S3 or Cloudinary. For a system that had to run at no infrastructure cost, this was the honest trade: it works, and thumbnails are served straight from Drive's CDN parameters.

The cost: Drive is not a CDN and rate-limits differently, and the coupling to a Google service account is a real operational dependency. It is the piece I would move first if this needed to scale.

Two things stand out now that the system is complete.

The first is authorization. Spreading checks across handlers was the right call for the relational rules, but I let it become the rule for everything, including the coarse cases where a middleware would have been safer and shorter. The audit log makes it possible to verify after the fact who called what, which softens the problem, but verifying after the fact is not the same as preventing it.

The second is that I modelled the appointment as a single row that changes status, rather than as an event log. Status transitions are where most of the clinic's actual questions live: how long requests sit before approval, how often they get rejected and rebooked, which vets get assigned most. All of that is currently reconstructable only from the audit log, when it should have been first-class data on the appointment itself.