Saying Goodbye to Airtable

I've been using Airtable for the last couple of years to keep track of all my runs. It's worked really well, and I've started using it to track more things as well.

However, I've always been a little uneasy about sharing the data with so many companies along the way.

Currently, when I log a run on my form, it gets passed through Formcarry to Zapier, which then adds it to the DB and (eventually!) triggers a site rebuild. Don't get me wrong, it all works, but I've never loved the idea of passing my information through so many for-profit companies. As I'm trying to let the principles of the indieweb govern this site, it doesn't sit well with that either.

I decided I wanted to begin moving off Airtable. Supabase had caught my eye as an 'open-source Firebase alternative', which sounded good to me! I'd played around with it before, decided it had potential, then put a pin in it and left it for a few months. Now was the perfect time to have a go!

I was also really interested in trying out serverless functions. I'm most familiar with Python, so I wanted to use Flask if at all possible. I discovered that this would work if I hosted this project on Vercel, so I began to build the new running log backend!

I started by importing the last two years of running data into Supabase. This was really easy - all I had to do was upload a .csv with all the data from Airtable, and Supabase chucked it into a table! Simple!

Next, I wanted to create an endpoint for 11ty to access, so when rebuilding this site I could point the page at that endpoint, and grab all of the data needed!

Supabase made it really easy to connect through Python, and Flask made it really easy to begin rendering the data in a JSON format!

@app.route('/data')
def runningData():
    response = (
        supabase.table("runningData")
        .select("*")
        .order("Date", desc=True)
        .execute()
    )

    # Accessing the data part of the response
    response_data = response.data  # This is where the actual data is stored

    # Return a JSON response using Flask's jsonify
    return jsonify(response_data)

This was all going well. Next, I needed to handle the more complicated data input form. This needed to replace Formcarry, and push the data through to the DB.
To do this, the form would send the data to a new endpoint using a POST request. This data would then be carried into the function, processed (to calculate information like stride length, etc.) and then sent to the DB.
A site rebuild would then be triggered, removing all other steps from the process! It would all be handled by my little Python script!

@app.route('/newRun', methods=['GET', 'POST'])
def addRun():
    try:
        # Getting form data
        date = datetime.strptime(request.form.get('date'), '%Y-%m-%d').date()
        startTime = datetime.strptime(request.form.get('starttime'), '%H:%M')
        endTime = datetime.strptime(request.form.get('endtime'), '%H:%M')
        distance = float(request.form.get('distance'))
        duration = endTime - startTime
        calories = request.form.get('calories')
        steps = int(request.form.get('steps'))
        heartPoints = request.form.get('heart')
        dog = request.form.get('dog')
        notes = request.form.get('note')
        runID = ''.join(random.choices(string.ascii_letters + string.digits, k=9))
        dayOfWeek = calendar.day_name[date.weekday()]  # e.g., 'Wednesday'

        # Calculate duration (endTime - startTime)
        durationSeconds = duration.total_seconds()  # Duration in seconds

        # Calculate pace (in total seconds per km)
        pace_seconds_per_km = durationSeconds / distance
        pace_minutes = int(pace_seconds_per_km // 60)  # Whole minutes
        pace_remaining_seconds = int(pace_seconds_per_km % 60)  # Remaining seconds
        pace = f"{pace_minutes}:{pace_remaining_seconds:02d}"  # Format pace as "MM:SS"

        # Calculate stride length (in meters per step)
        stride = (distance * 1000) / steps if steps > 0 else 0  # Avoid division by zero

        # Print the calculated values for debugging
        print(f"ID: {runID}")
        print(f"Day of the week: {dayOfWeek}")
        print(f"Duration (seconds): {durationSeconds}")
        print(f"Pace (MM:SS): {pace}")
        print(f"Stride length (meters/step): {stride}")

        # Insert into Supabase
        response = (
            supabase.table("runningData")
            .insert({
                "Date": date.strftime('%Y-%m-%d'),
                "ID": runID,
                'Day': dayOfWeek,
                'StartTime': startTime.strftime('%H:%M:%S'),
                'EndTime': endTime.strftime('%H:%M:%S'),
                'Duration': durationSeconds / 60,  # Total duration in minutes
                'Distance': distance,
                'Pace': pace,  # Use formatted pace
                'Stride': round(stride, 2),
                'Calories': calories,
                'Steps': steps,
                'HeartPoints': heartPoints,
                'Dog': dog,
                'Comments': notes
            })
            .execute()
        )

        requests.post('https://api.netlify.com/build_hooks/abcdefghijklmnop1234567890')

        # Returning relevant information in the response
        return 'Run saved!'
    except Exception as e:
        # Return error response in case of any issues
        return f"An error occurred: {str(e)}", 400

Ignoring my disgusting code, this worked well in my testing! I now wanted to roll it out to the main website, so after a bit of testing locally, I pushed the update!

Everything built successfully, and it's working well so far!

I'll try and check back in in a month or so to let you know how it's going, but so far so good!

Update - 24 October

Having left it to it for a few hours, I'd noticed that roughly 50% of the Netlify builds were failing. This was apparently random, but a cause for concern - the builds for this site pretty much never fail, so it was clearly something to do with this change.

I worked out that the issue was caused by the Vercel functions 'sleeping'. If nobody accessed the endpoint for a while, it took a few seconds to wake up again, and if it took too long, the build gave up and crashed!

Not ideal, so I decided to take a different approach. The 11ty site now goes to Supabase directly, and fetches the content - the API endpoint now only proccesses data from the form, and puts it into the ideal format for my site.
This is now working well, which is perfect!