Build a Maintenance Tracker in Flask (Part 1: The Data Model)

Last week I wrote about why so many fuel stations still run maintenance on WhatsApp and a spreadsheet nobody fully trusts. A few people asked, reasonably, "Okay, so what's the alternative that doesn't cost six figures and a software rollout team?" Fair question. So I'm building one, in public, starting from the smallest useful piece.

This isn't going to be a polished, production-grade SaaS by the end of this series. It's going to be a working Flask app that actually solves the problem for a small operation: a handful of stations, a handful of assets, one person who needs to know what broke, when, and what was done about it. If it grows from there, great. But I'd rather ship something small and real than something ambitious and half-finished.

Part 1 is just the data model. Boring, I know. But get this wrong early, and you'll be fighting it for months.

Figuring Out What Actually Needs to Exist

Before writing a single class, I sat with a notepad and asked: what are the actual "things" in this problem?

Turns out there are really only three that matter to start:

Equipment:

A dispenser, a submersible pump, an ATG unit, a generator. The physical thing that can fail.

Maintenance Log:

A record that something happened to a piece of equipment, on a date, done by someone, with some outcome.

Station:

Because most operators aren't running one location, they're running several, and "which station" is a question you'll be asking constantly.

I almost added a fourth, a separate "Fault" model, distinct from "Maintenance." Spent a good twenty minutes going back and forth on it. In the end I decided a fault is just a maintenance log with a specific type, rather than a whole separate table. Might be wrong about that. If this gets more complex later, I can always split it out. But starting simple and splitting later is a lot less painful than starting complex and merging things back together, so that's the bet I'm making.

The Models

Here's where I landed, using Flask-SQLAlchemy:

# python
from datetime import datetime
from app import db

class Station(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(120), nullable=False)
    location = db.Column(db.String(200))

    equipment = db.relationship('Equipment', backref='station', lazy=True)

    def __repr__(self):
        return f'<Station {self.name}>'


class Equipment(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    station_id = db.Column(db.Integer, db.ForeignKey('station.id'), nullable=False)
    name = db.Column(db.String(120), nullable=False)
    equipment_type = db.Column(db.String(50))  # dispenser, submersible pump, ATG, generator
    serial_number = db.Column(db.String(100))
    installed_on = db.Column(db.Date)

    logs = db.relationship('MaintenanceLog', backref='equipment', lazy=True)

    def __repr__(self):
        return f'<Equipment {self.name} ({self.equipment_type})>'


class MaintenanceLog(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    equipment_id = db.Column(db.Integer, db.ForeignKey('equipment.id'), nullable=False)
    log_type = db.Column(db.String(50))  # preventive, corrective, fault
    description = db.Column(db.Text, nullable=False)
    performed_by = db.Column(db.String(120))
    performed_on = db.Column(db.Date, default=datetime.utcnow)
    downtime_hours = db.Column(db.Float, default=0.0)

    def __repr__(self):
        return f'<MaintenanceLog {self.log_type} on {self.equipment_id}>'

A few things I want to point out, because they weren't obvious to me the first time I wrote something like this.

downtime_hours on the log, not on the equipment. My first instinct was to add a "total downtime" field directly on the Equipment model. Don't do that. It's a derived number — you'd have to keep it in sync every time a log gets added, edited, or deleted, and eventually it'll drift out of sync with reality. Just store the raw downtime per incident and calculate totals with a query when you need them. Slightly more work upfront, way less pain later.

log_type as a plain string, not an Enum, at least for now. I went back and forth on this too. SQLAlchemy supports Enums properly, and it's the "correct" way to do it. But early on, when you're not 100% sure what categories you'll actually need, a string is easier to extend without a migration. I'll probably switch to an Enum once the categories stabilize. That's a "later Regha" problem.

The backref on both relationships. This is what lets you write equipment.station.name from an equipment record, or station.equipment to get every piece of equipment at a station, without writing a single extra query by hand. If you're newer to SQLAlchemy, this is worth sitting with for a minute; it's one of those things that feels like magic until you understand it's just SQLAlchemy generating the reverse-lookup for you.

What This Doesn't Handle Yet

On purpose, this version has no users, no authentication, no permissions, no photo uploads for fault evidence, nothing about scheduling or reminders. All of that matters eventually. None of it matters before the core question, "can I record what happened to this piece of equipment and pull it back up later"? Actually works cleanly.

I've seen projects (including a couple of my own, if I'm honest) get stuck for weeks because someone tried to design the permissions system before the basic data even had a home. I'd rather get this boring part right first.

Next Up

Part 2 picks up from here, actually writing to these models through a form, and building the view that lists maintenance history per piece of equipment, sorted the way a station manager would actually want to read it. If you're following along and building this yourself, the repo will be linked once it's public.

If you've built something similar, even a rough internal tool at your own company, I'd like to know what you'd have done differently with these three models. Genuinely asking, not being polite about it.