Forem: Charlotte Towell

Desktop Music Player Using QT for Python - Built with Github Copilot CLI

Charlotte Towell — Sat, 31 Jan 2026 03:43:45 +0000

This is a submission for the GitHub Copilot CLI Challenge

What I Built

I have been really into media ownership lately. I have started digitising my vinyl collection this year (using a raspberry pi!) with the goal of abandoning Spotify. Through this, I discovered foobar2000 - a freeware audio player for local music files.

While it's good, I decided I wanted to customise my own player to have full control over the style and keep the functionality limited to my needs. Yes I could make a custom skin, but I've never ventured into desktop development before and in the current age of AI fully customised software is more accessible than ever so why not make my own from scratch!

For this challenge, I built a customised audio player desktop application - specifically with an audio visualiser feature so that I can play it on a mini screen in my PC set-up.

Demo

Some of the key features of the application are:

viewing your library by album, artist, year or folder
integrated media controls using keyboard shortctus like space bar for play & pause
queue management including the ability to go back to previously played tracks
detecting most supported audio files from a set destination folder
the audio visualiser + mini-player which are my personal favs
both windows & linux support (or at least Raspberry Pi OS which is what I tested myself)

You can check out the github repo here:

charlottetowell / desktop-music-player

A desktop music player for local audio files - For the Github Copilot CLI Challenge

Desktop Music Player

A desktop audio player for Windows & Linux with real-time visualization, built with Python and Qt.

Built for the Github Copilot CLI Challenge hosted by dev.to. See my entry blog + demo here: Desktop Music Player Using QT for Python - Built with Github Copilot CLI

In this README:

Features 🎶

Cross-Platform: Windows & Linux support
OS Media Key Integration: Control playback with keyboard media keys
Mini player: A pop-out mini player window with audio waveform visualisation
Audio Playback: Full playback engine with controls and playback history
Queue Management: Add, reorder, and remove tracks with drag-and-drop
Library Scanner: Auto-discover audio files with metadata extraction (MP3, FLAC, WAV, OGG, M4A, AAC)
Real-Time Audio Visualizer: Waveform display with smooth animations

Tech Stack 💻

UI: PySide6 (Qt for Python)
Audio…

View on GitHub

My Experience with GitHub Copilot CLI

tldr; lots of planning for context + very autonoumous copilot == fast iteration to usability!

My usual experience with AI-assisted coding is in more of a hands-on, keep a short leash kind of methodology. Iterating fast on very small scoped micro-tasks back and forward with the models.

For this project, to give Github Copilot CLI a real test, I approached it very differently by essentially staying in planning & design mode and letting it do all the heavy lifting with programming itself.

I have also never built a desktop application before. Most of my work is in web development or backend scripts so it was exciting to see how fast this brand new tooling can come together with the help of AI.

I approached my workflow with Github Copilot CLI in four stages:

Planning & Design + Initial Project Setup
Rapidly Building Out Features & Functionality (Ignoring UI)
Design Refinement - Layout & Styling Polish
Finalisation - Build + Documentation

Day One 🗓️

On day one, I spent my time drafting up the features I wanted to include, and working on a rough (to be replaced) design in figma. I used different AI models such as Gemini to teach myself an intro to desktop application development, which frameworks or libraries to use, and common gotchas to consider such as performance, all to inform my context for the project scope.

From this, I wrote my initial system prompt, chucked this back at AI once more, and resulted in my final copilot-instructions.md file.

This initial work set a good stage as I then prompted Copilot CLI with a simple prompt to use its instructions and spin up the skeleton of our project:

As per the repository's copilot instructions, create the initial skeleton of the project including folder structures and a README explaining how to run locally

Day Two 🗓️

Day two was all about building out the core functionality and my feature wish-lists. I ignored design for the moment here and focused purely on functionality. This consisted of giving Copilot more in-depth prompts about each specific function and then letting it roll to build it out in full.

This was similar to my usual back and forward approach with AI but I found Copilot was competent enough to complete features in full a lot of the time when given a detailed prompt, with often only one follow-up needed to address any bugs found from a short test.

Day Three 🗓️

Day three was when I decided I was done with my initial design in figma and wanted to entirely do over. Which was great considering I spent the prior session ignoring design anyway. I did however keep a peach element in the logo as a nod to my original idea.

After creating a mock-up of my new design, I then uploaded screenshots to my repo and instructed Copilot to update the styling to match.

My findings here is that Copilot is not fantastic at handling an overall large design brief and then applying globally to the application. However, when briefing smaller components at a time (eg. left panel, middle panel, right panel) and complementing with some written context, it does a passable job at scaffolding out the desired layout to match the design, and certainly better to prompting a model with no image input.

Finally 🏆

At last, once the app was in a good-enough looking state for me, I used Copilot to help with the final project tasks such as compiling the application for download, and creating clear documentation. It even managed to debug some installation issues including with the librosa library using lazy loading which wasn't compatible with our built application.

For installation, Copilot generated a build.spec file and windows + linux scripts to build the application using PyInstaller.

Summary

This was a fun little project to make, and not something I would have attempted without AI in a format I'm unfamiliar with. Although there is a million features & improvements I could add, I am quite satisified with it meeting the use case I wanted which was ultimately to display a cute waveform on a screen in my PC set up. I think the power of AI is not just in commercial use-cases, but in enabling personal side projects like this to rapidly get to a point of completion where you can create fully customised software for everyday life.

Digitising Vinyls Using Audacity & a Raspberry Pi

Charlotte Towell — Tue, 27 Jan 2026 10:54:10 +0000

My latest side project has been digitising my vinyl collection to ultimately abandon Spotify by the end of the year.

Is this something I could have achieved by just plugging my laptop into the record player? Of course, but that comes with a number of limitations, mainly so - lack of portability.

Enter - my Raspberry Pi

Small, nimble, and perfect to sit on the back of the shelf unassuming. Having a micro-computer really shines in situations where the micro aspect can come in handy.

The Recipe

1 USB Enabled Record Player (Mine is the Audio Technica LP60XUSB)
1 Raspberry Pi with at least a USB port
1 USB-B to USB-A cable

Step 1 - Boot your Raspberry Pi
First things first, we assume your raspberry pi is already booted with something like Raspberry Pi OS, conected to your network, and SSH enabled. I do a headless set-up following this Tom's hardware guide.

Step 2 - Set up VNC
If you've set up SSH correctly, this is as easy as installing a VNC viewer client like TigerVNC on another device of choice, then connecting with your SSH credentials.

Step 3 - Install Audacity
Next, install Audacity on your pi & main device - it's a free audio recording and editor software. You can opt to only install it on the pi and do your editing directly via VNC, but I prefer transferring the raw files over and using the power of my desktop to split tracks.

Now you're ready to record!

Top tip: using a VNC viewer app on your phone makes it easier to walk around the house listening to the record and being ready to stop or pause the recording when you need to flip sides!

Now that you've recorded both sides of a record. The next step is to export the full (unsplit) audio as a .wav file.

I then use scp to transfer these files onto my main desktop for editing. Example usage of this (assuming current directory is something like raw-vinyl-albums:
scp <user>@raspberrypi.local:Music/raw/<album_name>.wav .

Now you have the the full album recording locally, you can use Audacity on your desktop to split tracks, export as mp3, add metadata & album art (check out MP3 tag) and transfer your new audio files to as many devices as you want since you own the files!

Yay for media ownership! And for integrating a raspberry pi into my vinyl setup!

Using Terraform to Configure BigQuery Data Transfer Service for Google Ads & GA4

Charlotte Towell — Tue, 13 Jan 2026 05:32:17 +0000

The benefit of staying within the Google ecosystem shines through when it comes to syncing data into BigQuery with the Data Transfer Service. Rather than building our own API-based integrations and handle scheduling, it is almost* completely handled for us in the case of Google Ads & GA4!

Note the *almost. For an e-commerce use case, an acceptable delay can be to always have yesterday's data available. Unfortunately when it comes to BQDTS, we are working moreso with a 2-day up to date window on the automated syncs.

With a lot of pulling my hair out and trying to figure out if a timezone conversion was failing me I came to the discovery that:

Scheduled data transfers can only do as frequent as 2 days in the past
Manually triggered backfills for a 'date range' has this same limitation
Manually triggered backfills for 'run one-time transfer' magically do pull through the latest data available up until the current date

Therefore, using a combination of the normal scheduled sync + an automated 'manual' triggered sync, we can achieve our yesterday's-data availability method. To do this, I used self scheduling cloud tasks which I recently posted about to handle the daily 'one off transfer' sync.

BQDTS doesn't just run the one latest day per schedule but rather refreshes up to 7 days in the past each run, so we can be assured that our partial data will always be updated in full the following day.

This system has been running well for a little while for me now. A slight hassle? absolutely. But I still much prefer the ease of using Google's transfer service to handle all my data processing without writing the integration myself from scratch.

Without further ado, here are the code examples used to make this run:

Create the destination dataset

resource "google_bigquery_dataset" "skipper_au_google_analytics_ga4" {
  dataset_id                 = "skipper_au_google_analytics_ga4"
  friendly_name             = "Skipper Dataset for Google Analytics GA4 - AU"
  description               = "Dataset for Skipper Google Analytics GA4 BigQuery Data Transfer Service Integration"
  location                  = "australia-southeast1"
  project                   = var.project_id
  default_table_expiration_ms = null
}

Configure BigQuery Data Transfer Service with an 'every day' frequency

resource "google_bigquery_data_transfer_config" "google_analytics__ga4_transfer" {
  project                = var.project_id
  display_name           = "Google Analytics GA4 Transfer"
  location               = var.region
  data_source_id         = "ga4"
  destination_dataset_id = google_bigquery_dataset.skipper_au_google_analytics_ga4.dataset_id
  schedule               = "every day 18:00" #5AM AEST
  service_account_name   = var.bq_data_transfer_sa_email

  params = {
    property_id           = var.google_analytics_property_id
    table_filter          = "Audiences,DemographicDetails,EcommercePurchases,Events,LandingPage,PagesAndScreens,Promotions,TechDetails,TrafficAcquisition,UserAcquisition"
  }

  depends_on = [google_bigquery_dataset.skipper_au_google_analytics_ga4]
}

And now, the trick to enable our latest day stats coming through:

Call the manual transfer run via API

from google.cloud import bigquery_datatransfer_v1


def manual_bigquery_data_transfer_run(TRANSFER_CONFIG_ID: str):

    client = bigquery_datatransfer_v1.DataTransferServiceClient()

    LOCATION = "<location>"
    parent = f"projects/{GCP_PROJECT_ID}/locations/{LOCATION}/transferConfigs/{TRANSFER_CONFIG_ID}"


    # Use proper UTC+10 timezone object
    tz_utc_plus_10 = timezone(timedelta(hours=10))
    now_utc10 = datetime.now(tz_utc_plus_10)
    run_time = now_utc10 - timedelta(minutes=30)

    request = bigquery_datatransfer_v1.types.StartManualTransferRunsRequest(
        parent=parent,
        requested_run_time=run_time.isoformat(),
    )

    response = client.start_manual_transfer_runs(request=request)
    return response

Self-Scheduling Recurring Cloud Tasks (with Terraform + Python code)

Charlotte Towell — Mon, 12 Jan 2026 09:48:22 +0000

Using a fully serverless cloud set-up, sometimes the easy questions like: "how can I run this function on a recurring basis?" are not so easy to answer. CRON job? nope.

Instead, this is how I achieve it utilising scheduled Cloud Tasks for a fully serverless approach.

Let's say we have a Cloud Run Function we want to call on a daily, or perhaps weekly, basis. The process we'll follow is:

Set up a Cloud Tasks queue
Set up the function to reschedule itself
Create our initial task to set off the chain

Service Account

First, we create the service account used to run our cloud function

resource "google_service_account" "<my_sa>" {
  account_id   = "<my_sa>"
  display_name = "My Service Account"
  description  = "Service account used for my function"
  project      = var.project_id
}

Then we add all required roles, specifically those for Cloud Tasks & invoking Cloud Run

resource "google_project_iam_member" "<my_sa>" {
  for_each = toset([
    "roles/run.invoker",
    "roles/cloudtasks.enqueuer"
  ])

  project = var.project_id
  role    = each.value
  member  = "serviceAccount:${google_service_account.<my_sa>.email}"

  depends_on = [google_service_account.<my_sa>]
}

and finally, we also need to allow the service account to impersonate itself for queuing the next cloud task:

resource "google_service_account_iam_member" "<my_sa_self_impersonation>" {
  service_account_id = google_service_account.<my_sa>.name
  role               = "roles/iam.serviceAccountUser"
  member             = "serviceAccount:${google_service_account.<my_sa>.email}"
}

Cloud Task Queue

Next, create a cloud task queue.

resource "google_cloud_tasks_queue" "<my_task_queue_nane>" {
  name     = "<my_task_queue_name>"
  location = var.region

  http_target {
    http_method = "POST"
    oidc_token {
      service_account_email = <my_sa_email>
    }
  }
}

Queue Task from Function

Then, we can add a final bit of code to the end of our function to allow scheduling the next task. This is where we will set any rules like how long to schedule (1 day ahead, 1 week, etc), and what time of day to run at for example. Any shared data to be passed along must be passed as the HTTP body, and the function entry set up to parse this accordingly.


def cloud_task_scheduler(days_increment: int = 1):
    try:
        client = tasks_v2.CloudTasksClient()
        parent = client.queue_path(GCP_PROJECT_ID, LOCATION, CLOUD_TASK_QUEUE_ID)
        FUNCTION_URL="<my_cloud_function_url>"

        # define next task
        payload = {
            "schedule_next_cloud_task": True
        }
        body_bytes = json.dumps(payload).encode("utf-8")

        scheduled_date = (datetime.now(timezone.utc) + timedelta(days=days_increment)).replace(
            hour=16, minute=0, second=0, microsecond=0
        )
        ts = timestamp_pb2.Timestamp()
        ts.FromDatetime(scheduled_date)

        task: tasks_v2.CreateTaskRequest = {
            "http_request": {
                "http_method": tasks_v2.HttpMethod.POST,
                "url": f"{FUNCTION_URL}",
                "headers": {"Content-Type": "application/json"},
                "body": body_bytes
            },
            "schedule_time": ts
        }

        # add task to queue
        client.create_task(parent=parent, task=task)

    except Exception as e:
        raise Exception(f"Error scheduling next Cloud Task: {e}")

Schedule the First Task

To set the chain off running, we must schedule the first cloud task. Simplest way is via the command line with Google Cloud CLI.

gcloud tasks create-http-task my-first-task \
  --queue=my-queue \
  --url="my-function-url" \
  --method=POST \
  --body-content='{"schedule_next_cloud_task": true}' \
  --header="Content-Type":"application/json" \
  --oidc-service-account-email="my-sa-email" \
  --location="region" \
  --schedule-time="YYYY-MM-DDTHH:MM:SS+00:00"

And just like you should see your first task sitting in the queue waiting to run! Of course, I reccomend testing first to ensure that your function can run fully and then create irs following task successfully.

This approach can be used for a daily schedule like I've outlined above or even to split up long-running tasks that may exceed timeouts by either passing shared data through the body of the task request or writing to a database

The benefit is because the cost of Cloud Tasks is basically nothing, or literally free if you are below certain usage tiers, then this method is extremely efficient as we only pay for compute while our function is running and it can effectively scale to zero while waiting for the next cloud task to run.

Hopefully this helps anyone else after a simple approach of implementing a self-sustainaing schedule with serverless compute efficiently!

Git Integrated Workflow for Shopify Theme Development

Charlotte Towell — Mon, 12 Jan 2026 09:26:01 +0000

The very first port of call when onboarding to any ecommerce business using Shopify should be to ask the single question: how do you back-up your theme code?

And if the answer isn't git, you've now got your first task!

Below is the end-to-end workflow I now use to manage our Shopify theme versioning via the Shopify Github Integration.

Shopify CLI

Other than the obvious like git, you'll want to make sure you also have Shopify CLI installed so you can get started with shopify theme pull

Branches

Unlike most software development which generally has a single release candidate, ecommerce stores often go through different themes correlating to campaigns or product releases. For this reason, I don't use the main branch as our live theme but rather the most up-to-date stable version of the core theme.

Instead, I opt for the following branching strategy:

development - current development branch
review - "Review Only" theme synced to Shopify for other team-mates to review
main
live-theme - current live theme in Shopify

Note that these branches are not necessarily persistent (except for main) but are usually like live-theme-campaign-X.

This allows asynchronous development and marketing reviews without impacting our current live theme, as well as the ability to prepare for future theme changes like sale events. By ensuring that the currently published theme is connected to a branch, we retain the ability to push any urgent updates or bug fixes while still allowing any direct edits to be made in Shopify.

Pull Requests

Pull requests are always made to the main branch, documenting the primary line of changes & updates. Updates to a live theme are still first merged to main, and then main merged into the live-theme.

Tagging

An optional step which we implement is creating tags to label key releases, usually the accumulation of many PR merges to main at the point where it becomes a new live theme at the beginning of a campaign. For this we have another branch bau which we merge to from main via a PR, and a Github action that auto-creates the tag. The main point of this is more for historical tracking of themes mapped to campaigns, but is a great way to mark available backup points.

This is the process that works for my small team where it is primarily me working on the theme with some input from other colleagues. A good balance of maintaining git best practise without overcomplicating for the scale we need.

Google Sheets x BigQuery Sync using Google Apps Script

Charlotte Towell — Mon, 13 Oct 2025 06:33:45 +0000

If you haven't discovered Google Apps Script yet (ahem, me a week ago) then you are seriously missing out!

This free service included in the Google Suite can be extremely powerful when wielded with the right knowledge. I recommend checking out Benson King'ori's dev.to article for a super in-depth explanation & intro of apps script.

Here's how I used it to set up a user-trigger Google Sheets -> BigQuery sync

tldr; save a JSON service account key in PropertiesService to access any GCP API. You're welcome :)

Prerequisities

You'll need to have a Google Cloud Project & a Google Workspace to work in.

Optional, but I'd also suggest installing clasp - a CLI for working with apps script locally.

Credentials

Perhaps the most obvious hurdle to tackle here is how to ensure secure authentication with our GCP project when initiating a sync from a user in Google Sheets. This is where we'll use a json service account key and the script's PropertiesService to save it.

This is the terraform snippet I used to create my service account in GCP, granting only the necessary permissions for BigQuery sync:

resource "google_service_account" "google_apps_script_sa" {
  account_id   = "google-apps-script-sa"
  display_name = "Google Apps Script Service Account"
  description  = "Service account for Google Apps Script"
}

resource "google_project_iam_member" "act_as_google_apps_script_sa" {
  for_each = toset([
    "roles/bigquery.dataEditor",
    "roles/bigquery.jobUser",
    "roles/bigquery.readSessionUser",
    "roles/bigquery.user"
  ])

  project = var.project_id
  role    = each.value
  member  = "serviceAccount:${google_service_account.google_apps_script_sa.email}"

  depends_on = [google_service_account.google_apps_script_sa]
}

Once created, you an generate a JSON service account key and then store it (joined as a single line) in the PropertiesService of the script.

User Interaction

I created my script as "Bound" to a certain Google Sheets file, and within it, I wanted the user to select which Sheet to sync. Therefore, we can create a .html file in our project to define the popup dialog, hooking up a 'Sync' button to a google.script.run block as follows:

$('submitBtn').addEventListener('click', function (e) {
  // ...

    google.script.run
      .syncToBigQuery(...);
  });

We can then include this dialog as a custom menu option in Sheets by defining a createMenu item that shows the popup as a modal dialog.

// Menu.js
function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('Data Tools')
    .addItem('Sync to BigQuery', 'showSheetSelector')
    .addToUi();
}
=
function showSheetSelector() {
  const t = HtmlService.createTemplateFromFile('Dialog');

  const html = t.evaluate()
    .setWidth(420)
    .setHeight(260);

  SpreadsheetApp.getUi().showModalDialog(html, 'Sync Sheet to BigQuery');
}

Authentication

Now for where the real magic happens, to make use of our service account key, we use the following function to mint an oauth2 token that will allow us to interact with BigQuery.

function getServiceAccountToken_(scopes) {
  const raw = PropertiesService.getScriptProperties().getProperty('SA_KEY_JSON');
  if (!raw) throw new Error('Missing SA_KEY_JSON in Script Properties');
  const key = JSON.parse(raw);

  const service = OAuth2.createService('sa-jwt')
    .setTokenUrl('https://oauth2.googleapis.com/token')
    .setPrivateKey(key.private_key)
    .setIssuer(key.client_email)
    .setPropertyStore(PropertiesService.getScriptProperties())
    .setScope(scopes.join(' '));

  const accessToken = service.getAccessToken();
  if (!accessToken) throw new Error('Failed to obtain access token: ' + service.getLastError());
  return { accessToken };
}

Using it is as simple as:

 const { accessToken } = getServiceAccountToken_([
    'https://www.googleapis.com/auth/bigquery'
  ]);

The possibilities are endless

It's clear how easy this is to extend to any other GCP service! All you have to do now is cross-reference the docs and form your Rest API calls to BigQuery's API. The only limitation to what you can do here is your imagination...

...and of course:

The API's available to us for GCP Services
And the associated runtime limits with Google Apps Script

Nonetheless, this proved a simple & highly suitable method to my use case to allow non-technical users who occasionally update a lookup spreadsheet to keep data in sync with our BigQuery warehouse. A full-fledged scheduled sync would've been overkill for a job so small and infrequent. Having a custom menu in Google Sheets was the perfect solution of meeting the user where they're at.

Using GCP Load Balancer to Handle 301 Redirects to Other Domains

Charlotte Towell — Tue, 29 Jul 2025 00:36:56 +0000

Some of the main use cases of using a load balancer include setting up a static IP for use with a custom domain, or by dispersing traffic across multiple backend services.

A possibly less common use-case however is to use the balancer to handle 301 redirects in cases where your web application has undergone a domain migration. Here's how I set this up:

Configure DNS Zones > Recordsets

In the case of a domain migration, you must route the old domain(s) to a load balancer. I choose to use the same load balancer as used for my backend service to centralise the logic, else you may need a 'dummy' backend that is not reached.

Routing Rules

Assuming you already have an application load balancer set up in Google (if not, see here), we can jump straight into editing the routing rules of the load balancer

Keep your existing routing rules, including default, to route to your desired backend(s). We will be clicking Add host and path rule, specifically for the old domain.

For the Hosts, add in your old domain(s).
For path matcher, you can copy & paste the following template:

defaultService: projects/<project-id>/global/backendServices/<backend-service-id>
name: matcher1
routeRules:
- urlRedirect:
    httpsRedirect: true
    stripQuery: true
    hostRedirect: newdomain.com
    redirectResponseCode: MOVED_PERMANENTLY_DEFAULT
  matchRules:
  - prefixMatch: /auth/ //only do for oldomain/auth/*
  priority: 1
- urlRedirect:
    httpsRedirect: true
    stripQuery: true
    hostRedirect: newdomain.com/login
    redirectResponseCode: MOVED_PERMANENTLY_DEFAULT
  matchRules:
  - prefixMatch: /util/login //only do for oldomain/util/login/
  priority: 2
- urlRedirect:
    httpsRedirect: true
    stripQuery: true
    hostRedirect: newdomain.com
    redirectResponseCode: MOVED_PERMANENTLY_DEFAULT
  matchRules:
  - prefixMatch: /util/ //only do for olddomain/util/*
  priority: 3
- urlRedirect:
    httpsRedirect: true
    stripQuery: true
    hostRedirect: anotherdomain.com
    redirectResponseCode: MOVED_PERMANENTLY_DEFAULT
  matchRules:
  - prefixMatch: / //everything 
  priority: 4

Important Points:

Each rule (path matcher) must have a unique priority. Rules compute in cascading order, therefore you must put more specific matches first.
The MOVED_PERMANENTLY_DEFAULT code is what sets our 301 response
You can redirect to other domains & websites that are not the load balancer backend service, eg otherdomain.com
After the redirect to newdomain, it will then use your existing routing rules (if existing) to do any further routing before reaching the backend service(s)

At this point, you can save & test and should now successfully see the load balancer redirecting traffic from your old domain to new one based on whichever rules you have set!

Custom Domain for Web Apps on Cloud Run - How to Set Up Application Load Balancer on GCP

Charlotte Towell — Tue, 29 Jul 2025 00:09:57 +0000

This guide will walk through the steps of setting up a load balancer for Cloud Run instances in GCP. As well as the other features a load balancer offers, primarily our outcome will be a static outbound IP which we can then use in a DNS service of choice to configure a custom domain.

Some other articles of mine that may be of interest:

Let's get started!

Prerequisities

You should already have a Cloud Run instance deployed

Creating a Load Balanacer

This will roughly follow the guide as per Set up a classic Application Load Balancer in the docs.

Step 1: Create a new load balancer, choosing the following:

Application Load Balanacer (HTTP/HTTPS)
Public facing (external)
Best for global workloads
Global external Application Load Balancer

Step 2: Frontend Configuration
First, we need to reserve a static IP by clicking "Create IP address"

Keep the IP version as IPv4, keep the port as 443 (important for Cloud run!), and be sure to choose an active certificate. We will keep the "additional certificates" as GCP default.

Step 2: Backend Configuration
When creating the backend configuration, set the backend type as "Serverless network endpoint group". This then gives us the option to Add a backend and Create a new Serverless network endpoint group.

In Create Serverless network endpoint group, this is where you will select your Cloud Run instance which will receive all the traffic from the load balancer

If you intend to use your load balancer to disperse traffic over multiple backend services, this is where you can add these and later set routing rules to control which requests go to each.

In the meantime, we can leave everything else as the default options. Note that another thing you can do at this point is create a custom Cloud Armour policy for purposes such as IP whitelisting.

Step 3: Routing Rules
If you are only using the load balancer for a custom domain, then we can leave Routing rules as Simple host and path rule. If you set up multiple backend services in the previous step, this is where you can configure the routing for these.

Step 4: Review & Finalise
Finally, we review & finalise, and your options should look similar to mine in the image below:

Step 5: PubliC IP
Once the load balancer creation finished loading, you can then click on it's details to reveal the public IP.

Step 6:
You can now use this IP in your DNS provider of choice to configure a custom domain.

And done!

Now all traffic to your custom domain will be received by the load balancer, and then routed accordingly as per the routing rules you set to your backend Cloud Run service(s)!

Image source: Google Cloud

MySQL 5.7 - 8.0 on Google CloudSQL: Inplace Migration

Charlotte Towell — Wed, 16 Jul 2025 01:38:11 +0000

As of May 2025 - MySQL 5.7 is now in extended support for CloudSQL meaning your cloud bill is about to ⬆️⬆️⬆️

This is how I upgraded our CloudSQL instance(s) from MySQL 5.7 - MySQL 8.0 with minimal downtime via an inplace migration.

⚠️ Always clone your instance and complete these steps fully before performing on your production instance

Let's get started

Prerequisites:

Install MySQL Client - Download Link for MySQL Workbench
Install MySQL Shell - Download Link

Remember to add to your PATH variable so you can run the following commands:

mysql --version
mysqlsh --version

Connect to Instance From Cloud Shell

🔗 Google Docs: Connect Instance Cloud Shell

Set the root user password

>gcloud sql users set-password root
--host=%
--instance=<instance_id>
--prompt-for-password

Then connect to the instance to check it worked:

> gcloud sql connect <instance_id> --user=root
Allowlisting your IP for incoming connection for 5 minutes...done.

You can also now run status command to see the current version info

Now connect to mysqlsh for the purpose of saving password (otherwise you need to enter password many times when running the below utility checker script):

>mysqlsh root@<ip>
Please provide the password for 'root@<ip>': *********************
Save password for 'root@<ip>'? [Y]es/[N]o/Ne[v]er (default No): Y
MySQL Shell 9.3.0

Run the MySQL Upgrade Utility Checker

MySQL provides an Upgrade Utility Checker

Running as reccomended results in a timeout, instead use the following shell script which runs each check at a time, and saves to a file.

#!/bin/bash

# Colors for output
RED='\033[0;31m'
GREEN='\033[0;32m'
YELLOW='\033[1;33m'
CYAN='\033[0;36m'
NC='\033[0m' # No Color

# Prompt for IP address
echo -n "Enter MySQL server IP: "
read ip

# Create save directory
save_path="$HOME/Desktop/mysql-upgrade-checks"
mkdir -p "$save_path"

echo -e "\n${CYAN}Fetching available checks from $ip...${NC}"

# Script to list checks
script_to_list_checks='var checks = util.checkForServerUpgrade(null, { list: true }); print(JSON.stringify(checks));'

# Get list of checks
raw_checks=$(mysqlsh --js --uri root@$ip --execute="$script_to_list_checks" 2>&1)

# Parse the check names from the output
# Extract check IDs from the "- checkName" lines in the "Included:" section
checks=$(echo "$raw_checks" | awk '
/^Included:$/ { in_included = 1; next }
/^Excluded:$/ { in_included = 0; next }
in_included && /^- / { 
    # Extract the check name (first word after "- ")
    gsub(/^- /, "")
    print $1
}
')

if [ -z "$checks" ]; then
    echo -e "${RED}Error: Could not find any checks in the response.${NC}"
    echo "Raw output: $raw_checks"
    exit 1
fi

# Count checks
check_count=$(echo "$checks" | wc -l)
echo -e "${GREEN}Found $check_count checks.${NC}"

# Script template with placeholder
script_template='var result = util.checkForServerUpgrade(null, {
  targetVersion: "8.0",
  outputFormat: "JSON",
  include: ["__CHECK_ID__"]
});
print(JSON.stringify(result, null, 2));'

# Process each check
echo "$checks" | while read -r check_id; do
    if [ -n "$check_id" ]; then
        output_file="$save_path/$check_id.json"
        echo -e "${YELLOW}Running check: $check_id ...${NC}"

        # Replace placeholder with actual checkId
        script=$(echo "$script_template" | sed "s/__CHECK_ID__/$check_id/g")

        # Run the check and save output, filtering out "undefined"
        mysqlsh --js --uri root@$ip --execute="$script" 2>&1 | grep -v "^undefined$" > "$output_file"

        if [ -f "$output_file" ]; then
            echo -e "${GREEN}✔ Saved: $output_file${NC}"
        else
            echo -e "${RED}⚠ Failed to save output for $check_id${NC}"
        fi
    fi
done

echo -e "\n${CYAN}✅ All checks completed. Results saved to: $save_path${NC}"

You will need the public IP address of the instance which you can find from Google Cloud Console:

Additionally, make sure you are IP whitelisted for your CloudSQL instance, otherwise connection will be lost part-way through the checks if > 5 minutes.

Review the Upgrade Utility Checker Results

As per the above script, the results will be saved to JSON files in the specified location $HOME/Desktop/mysql-upgrade-checks.

For this step internally, I created a python file to parse the JSON files into a condensed easy-to-read report. Below is a snippet of this outputted report, I highly suggest writing a script to produce a similar output for review:

=============
MySQL UPGRADE COMPATIBILITY CHECK REPORT
=============

SERVER INFORMATION
----------------------------------------
Server Address: <ip:
Current Version: 5.7.44-google-log - (Google)
Target Version: 8.0.42

EXECUTIVE SUMMARY
----------------------------------------
Total Files Processed: 37
Total Errors: 92
Total Warnings: 3093
Total Notices: 14259
Checks with Issues: 8
Checks OK: 23

❌ UPGRADE BLOCKED: Critical errors detected that must be resolved before upgrade.

CHECK SUMMARY ANALYSIS
=============================
🚨 CRITICAL ERRORS (Must Fix) (1 checks)
--------------------------------------------------
Check: MySQL syntax check for routine-like objects
  Source: syntax.json
  Affects 1 unique objects
  Affects 92 databases/schemas
  Total occurrences: 92
  Sample objects: example_proc_name (Routine)

⚠️  WARNINGS (Should Address) (5 checks)
--------------------------------------------------
...
ℹ️  NOTICES (Informational) (1 checks)
--------------------------------------------------
...
CHECK-BY-CHECK SUMMARY
============================
...

Review this report and fix any critical issues accordingly. This may involve running ALTER statements to fix certain columns, or cleaning up unused legacy databases. When fixed, re-run the utility checker until you confirm there are no upgrade conflicts.

Take Backup of Instance

Note: that this is extra, as CloudSQL also takes automatic backups both pre & post upgrade

gcloud sql backups create --async --instance=<instance_id> --description=pre-mysql8-upgrade

Manually created backups are not deleted automatically, unless that instance is deleted. Google Docs Link

View the backup to confirm it was successfully made:

>gcloud sql backups list --instance <instance_id>
ID             WINDOW_START_TIME              ERROR  STATUS      INSTANCE
<backup_id>  2025-07-04T06:25:41.618+00:00  -      SUCCESSFUL  <instance_id>
<backup_id>  2025-07-04T04:05:16.766+00:00  -      SUCCESSFUL  <instance_id>

or view more detail about specific backup like:

>gcloud sql backups describe <backup_id> --instance <instance_id>
backupKind: SNAPSHOT
databaseVersion: MYSQL_5_7
description: pre-mysql8-upgrade
endTime: '2025-07-04T06:27:12.911Z'
enqueuedTime: '2025-07-04T06:25:41.618Z'
id: '<backup_id>'
instance: <instance_id>
kind: sql#backupRun
location: <location>
maxChargeableBytes: '<bytes>'
selfLink: https://sqladmin.googleapis.com/sql/v1beta4/projects/<project_id>/instances/<instance_id>/backupRuns/<backup_id>
startTime: '2025-07-04T06:25:41.627Z'
status: SUCCESSFUL
type: ON_DEMAND
windowStartTime: '2025-07-04T06:25:41.618Z'

Run Upgrade (Will cause some downtime!)

Get the database version for upgrade by running:
gcloud sql instances describe <instance_id> --format="table(upgradableDatabaseVersions)"

Choose the database version that you ran the MySQL utility checker against. You can see it at the top of the summary reports.

MYSQL_8_0_42

gcloud sql instances patch <instance_id> --database-version=<DATABASE_VERSION>

Depending on the size of your instance, this make take some time, up to 1 hour. The database will only be offline during a portion of this window.

You may get a timeout error which is fine, just run the following:

ERROR: (gcloud.sql.instances.patch) Operation https://sqladmin.googleapis.com/sql/v1beta4/projects/<project_id>/operations/<operation_id> is taking longer than expected. You can continue waiting for the operation by running `gcloud beta sql operations wait --project <project_id> <operation_id>`

List operations against the instance

>gcloud sql operations list --instance=<instance_id> --filter=STATUS=RUNNING
NAME            TYPE    START                          END  ERROR  STATUS
<operation_id>  UPDATE  2025-07-04T06:41:23.812+00:00   T     -    RUNNING

Use the operation ID to monitor the status

In the event of errors! Revert to backup

gcloud sql backups restore <backup_id> --resotre-instance=<instance_id>

Complete the upgrade

See CloudSQL upgrade guide for recomendations about additional needed testing i.e. updating user privileges. This will differ based on your instance configuration.

Google Docs Link

The main change needed will be to update your database user permissions.

GRANT ALL PRIVILEGES no longer works, so instead do this, tweaking based on which permissions you wish to grant to the user

GRANT
    ALTER,
    ALTER ROUTINE,
    CREATE,
    CREATE ROUTINE,
    CREATE TEMPORARY TABLES,
    CREATE VIEW,
    DELETE,
    DROP,
    EVENT,
    EXECUTE,
    INDEX,
    INSERT,
    LOCK TABLES,
    PROCESS,
    REFERENCES,
    SELECT,
    SHOW DATABASES,
    SHOW VIEW,
    TRIGGER,
    UPDATE
ON *.* TO `user`@`%`;

Finally, delete backups

After a period of time, if all is well, you can delete the manually created backup via:

>gcloud beta sql backups delete <backup_id> --instance=<instance_id>

You can also use the same method to delete the CloudSQL automatic backups, else they will persistent indefinitely.

Upgrade Complete!

Just like that, no more extended support bills to pay to Google Cloud :)

Customising your VS Code Integrated Terminals

Charlotte Towell — Tue, 08 Jul 2025 06:04:29 +0000

Ever wanted to create custom VS Code terminals? Me too!

If you're looking for a how-to this will show you how to:

Create custom integrated terminals
Customise the terminal colour, icon, and display name

Let's get into it!

Terminal Integrated Profiles

First things first, let's go into our integrated terminal settings.
Use ctrl+shift+p then open VS Code settings. Search for "Terminal integrated profiles windows" and then click "Edit in settings.json

The all-powerful `settings.json`

This is where all the magic happens!

{
    ....
    "terminal.integrated.profiles.windows": {
        "PowerShell": {
            "source": "PowerShell",
            "icon": "terminal-powershell",
            "color": "terminal.ansiBlue"
        },
        "Command Prompt": {
            "path": [
                "${env:windir}\\Sysnative\\cmd.exe",
                "${env:windir}\\System32\\cmd.exe"
            ],
            "args": [],
            "icon": "terminal-cmd"
        },
        "Git Bash": {
            "path": "C:\\Program Files\\Git\\bin\\bash.exe",
            "args": [],
            "icon": "github",
            "color": "terminal.ansiMagenta"
        },
        "Google Cloud SDK": {
            "path": "C:\\Windows\\System32\\cmd.exe",
            "args": ["/k", "C:\\Users\\<User>\\AppData\\Local\\Google\\Cloud SDK\\cloud_env.bat"],
            "icon": "cloud",
            "overrideName": "Google Cloud SDK",
            "color": "terminal.ansiYellow"
        }
    },
    "terminal.integrated.defaultProfile.windows": "Git Bash"
    ...
}

You will something like the above, specifically looking for the terminal.integrated.profiles.windows object.

Each object here will be available as a terminal option when creating a new terminal, so you can directly add in as many things as you want here!

Terminal Paths vs. PATH env variable

At this point you may be wondering about my custom "Google Cloud SDK" terminal...

By directly creating a terminal to the .bat file, you get an alternative for a easy-to-open dedicated path to run gcloud commands against, instead of making it globally available by adding to your system path (which of course you could still do as well~)

Customisation Settings

Other than the PATH itself, the real fun stuff is in customising how your terminal looks. You can see docs for the full available options here.

"Google Cloud SDK": {
            "icon": "cloud",
            "overrideName": "Google Cloud SDK",
            "color": "terminal.ansiYellow"
        }

Colours

To change the colours, just start typing as VS Code has intellisense for the available colour options.

Name

Setting the overrideName attribute lets you customise the displayed name of your integrated terminal in the UI.

Icon

Choosing the icon is perhaps the most fun part! I struggled to find a complete list of all available icons, so the best way to browse is by visiting any other "Icon" related setting in the UI, and looking through the dropdown of options.

All Together

We have a much more bright & colourful terminal experience! And if you're like me, much less error prone by clicking on the wrong one by accident.

My favourite use case of this is to have multiple of the same terminal "type" but styled different, so in my day-to-day workflows I keep certain tasks separated in each and it's easy to distinguish them.

Happy VS Code customising!

How to Set Up a Static Backend IP for Cloud Run Revision using VPC Connector

Charlotte Towell — Tue, 01 Jul 2025 06:46:43 +0000

When deploying services on Cloud Run, the default behaviour is that the backend IP address (that is, where requests to external endpoints come from within your app), is assigned from a dynamic IP address pool.

Therefore, for cases that require IP whitelisting, you need to configure the Cloud Run instance to use a static backend IP, which can be achieved through the magic✨ (read: networking capabilities) of VPC Connector.

Note that we are referring to the outbound IP here, not the inbound IP which instead is how traffic gets to our Cloud Run instance and can be configured via a load balancer.

Check out the Google Cloud docs here for static outbound IP addresses

How to Configure a Static Outbound IP?

Step 1: Create a Router

gcloud compute routers create my-router --network=default --region=my-region

Creating router [my-router]...done.
NAME                    REGION                NETWORK
my-router  my-region  default

Step 2: Reserve a Static IP

gcloud compute addresses create my-ip --region=my-region

Created [https://www.googleapis.com/compute/v1/projects/my-project/regions/my-region/addresses/my-ip].

Optional Step: View Existing Subnets

gcloud compute networks subnets list --network=default --filter="region:(my-region)"

NAME           REGION                NETWORK  RANGE          STACK_TYPE  IPV6_ACCESS_TYPE  INTERNAL_IPV6_PREFIX  EXTERNAL_IPV6_PREFIX
default        my-region  default  0.0.0.0/00  IPV4_ONLY
my-other-subnet my-region  default  0.0.0.0/00  IPV4_ONLY

In reality, your existing subnets will have actual IP ranges. Take note of this when choosing your new range so it is not equal to an existing one.

Step 3: Create a new Subnet

gcloud compute networks subnets create my-subnet --netwo rk=default --range=00.0.0.0/01--region=my-region

Created [https://www.googleapis.com/compute/v1/projects/my-project/regions/my-region/subnetworks/my-subnet].
NAME                    REGION                NETWORK  RANGE        STACK_TYPE  IPV6_ACCESS_TYPE  INTERNAL_IPV6_PREFIX  EXTERNAL_IPV6_PREFIX
my-subnet  my-region  default  10.0.0.0/24  IPV4_ONLY

Step 4: Create a Cloud NAT Gateway

gcloud compute routers nats create my-nat \
--router=my-router \
--region=my-region \
--nat-custom-subnet-ip-ranges=my-subnet \
--nat-external-ip-pool=my-ip

Use the names you configured in the previous steps here.

Creating NAT [my-nat] in router [my-router]...done.

Step 5: Set the Networking on your Cloud Run Revision

Important - If it's not working, confirm that it is set to route all traffic to the VPC, not just route only requests to private IPs to the VPC -- use case for private traffic is between google services eg. static IP for Cloud SQL in API endpoint cloud run revisions

Step 6: See the Static Outbound IP from Cloud NAT

And all done! To test all is working as intended, you can make an API request to services such as GET https://api.ipify.org?format=json from within your Cloud Run application.

Using Pub/Sub to Build a Serverless Async Processing Pipeline on GCP

Charlotte Towell — Tue, 20 May 2025 02:04:01 +0000

Google Cloud Run functions seem like the godsend of serverless computing until you hit reach the many different limitations all ultimately related to runtime. I'm talking about the max timeout in functions themselves (3600s), or other products such as API Gateway (300s).

This is the problem I faced at work where we had a long-running computation process that fires off as a result of an API call from a user. Sure, we could set the max deadline to 1 hour and have the user wait for 60 mins just to get a response, not. Plus, as I mentioned, API Gateway caps it at 5 minutes anyway.

This is an obvious use case for asynchronous processing with the ability to start a process, return a response, and keep it running...

... which is something not supported inherently in a serverless function which ceases running once returning a response.

Pub/Sub to the rescue

My solution: pub/sub messaging

Contrainsted to our serverless setup, one thing I've learnt about Google Cloud is that the limitations of one GCP product generally can be solved by combining with another.

Thus, my multi-step solution to a fully serverless, async processing pipeline with status updates.

The flow is as follows:

1. Client calls an API to start the job

This API generates a unique ID
Pubsub message is published with this unique ID, and any other attributes
Return the unique ID to the client

def publish_to_pubsub(data, attributes=None):
    publisher = pubsub_v1.PublisherClient()
    topic_name = 'projects/myproject/topics/async-messages'
    #convert to bytes
    data_bytes = json.dumps(data).encode('utf-8')
    #publish to topic
    future = publisher.publish(topic_name, data=data_bytes, **attributes)
    future.result() #wait for msg to be published

2. The pubsub topic has push subscribers, optionally with attribute filters. I use this to re-use the same topic for different async processes.

Receive the message and a subscriber cloud function runs
Check if the long-running process has started based on provided ID
If not started, make a HTTPS request to start the long-running cloud function, but don't wait for a resposne
We update the database with our ID to mark the function has started

gcloud pubsub subscriptions create EXAMPLE_WORKFLOW --topic=async-messages --push-endpoint=https://example-workflow-cloud-function.a.run.app --ack-deadline=10 --push-auth-service-account=pubsub-push-sa@myproject.iam.gserviceaccount.com --message-filter="attributes.workflow=\"Example Workflow\"" --dead-letter-topic=dead-letter-topic --max-delivery-attempts=5

from multiprocessing import Process

def send_request(url, body):
    authenticated_post_request(url=url, json_payload=body)

process = Process(target=send_request, args=(url, body))
process.start()

process.join(5) #cancel process
if process.is_alive():
   process.terminate()

At this moment, our long-running process has started, essentially on its own in the wide galactic space of Google's servers with no client to receive its response 🌌

3. Long running function does our actual processing

This is really just another cloud function now, but our only time constraint is the timeout of cloud functions themself as there is no client listening to a resposne
If for whatever reason you need a longer timeout (maybe assess why you're using serverless 🤔) you could chain the process of starting and abandoning cloud functions, so long as you continue to pass the id and any requried data
Periodically update the status of the process to provide incremental updates, via the database
- At this point, you could build more robust re-try handling by adding deadlines for certain status changes & resumability

4. We have another API endpoint to GET the current status.
This is just a database query based on the ID we provided back to the client

Idempotency ?

By using pub/sub, a key requirement is to ensure idempotency as messages are often sent multiple times. This is why the subcriber function must update the database to indicate the job has started.

Retries ?

Pub/sub also has a retry-backoff functionality. A downside of this set-up is that our long-running function does not benefit from this as it is only called once from the subscriber. However any logic we put into the subscriber function can throw an error to result in an unacked message.

So in summary,

Could we not have just started the long-running function from the initial API call?

Yes of course.

The reason I opted to use pub-sub is primarily to allow multiple subscriber functions to fire from messages. By using subscription message filters, you can split what would have been a long-running process into several smaller ones to run in parallel.

After all, with serverless, we want to scale horizontally first wherever possible :)

Forem: Charlotte Towell

Desktop Music Player Using QT for Python - Built with Github Copilot CLI

What I Built

Demo

charlottetowell / desktop-music-player

A desktop music player for local audio files - For the Github Copilot CLI Challenge

Desktop Music Player

Features 🎶

Tech Stack 💻

My Experience with GitHub Copilot CLI

Day One 🗓️

Day Two 🗓️

Day Three 🗓️

Finally 🏆

Summary

Digitising Vinyls Using Audacity & a Raspberry Pi

Enter - my Raspberry Pi

The Recipe

Using Terraform to Configure BigQuery Data Transfer Service for Google Ads & GA4

Create the destination dataset

Configure BigQuery Data Transfer Service with an 'every day' frequency

Call the manual transfer run via API

Self-Scheduling Recurring Cloud Tasks (with Terraform + Python code)

Service Account

Cloud Task Queue

Queue Task from Function

Schedule the First Task

Git Integrated Workflow for Shopify Theme Development

Shopify CLI

Branches

Pull Requests

Tagging

Google Sheets x BigQuery Sync using Google Apps Script

Prerequisities

Credentials

User Interaction

Authentication

The possibilities are endless

Using GCP Load Balancer to Handle 301 Redirects to Other Domains

Configure DNS Zones > Recordsets

Routing Rules

Custom Domain for Web Apps on Cloud Run - How to Set Up Application Load Balancer on GCP

Prerequisities

Creating a Load Balanacer

And done!

MySQL 5.7 - 8.0 on Google CloudSQL: Inplace Migration

Let's get started

Connect to Instance From Cloud Shell

Run the MySQL Upgrade Utility Checker

Review the Upgrade Utility Checker Results

Take Backup of Instance

Run Upgrade (Will cause some downtime!)

In the event of errors! Revert to backup

Complete the upgrade

Finally, delete backups

Upgrade Complete!

Customising your VS Code Integrated Terminals

If you're looking for a how-to this will show you how to:

Terminal Integrated Profiles

The all-powerful settings.json

Terminal Paths vs. PATH env variable

Customisation Settings

Colours

Name

Icon

All Together

How to Set Up a Static Backend IP for Cloud Run Revision using VPC Connector

How to Configure a Static Outbound IP?

Step 1: Create a Router

Step 2: Reserve a Static IP

Optional Step: View Existing Subnets

Step 3: Create a new Subnet

Step 4: Create a Cloud NAT Gateway

Step 5: Set the Networking on your Cloud Run Revision

Step 6: See the Static Outbound IP from Cloud NAT

Using Pub/Sub to Build a Serverless Async Processing Pipeline on GCP

Pub/Sub to the rescue

Idempotency ?

Retries ?

So in summary,

The all-powerful `settings.json`