For about a year my CloudFront log viewer lived on my laptop. One Python file, view.py, a few hundred lines of http.server with no framework, reading an SQLite file called data.db. When I wanted to see who’d been hitting the blog I’d run python view.py serve; it would download whatever new .gz log files had landed in S3, parse them, and put a dashboard on localhost:8765. That was the whole thing. It worked, and it pulled logs only when I remembered to open it, which was the problem.

Having just rebuilt the home Kubernetes cluster, I had somewhere proper to put it. The goal was modest: the dashboard always on at logs.jeakyl.com, a real database behind it, and the log pull happening on a schedule instead of when I felt like it. What I didn’t expect was that the move would surface three bugs that had been sitting quietly in the code the whole time, hidden by SQLite being forgiving in ways Postgres is not.

What it was

view.py does four things from one file: download syncs .gz CloudFront logs from s3://blog.jeakyl.com-logs, parse reads them into SQLite, geoip fetches the MaxMind country database, and serve runs the dashboard. The dashboard itself is a single ui.html plus a handful of JSON endpoints – /api/stats, /api/top_pages, /api/countries, /api/search, and a few others. Client IPs get a country code at parse time from a baked-in GeoLite2 database. Roughly thirteen thousand rows when I started, in one file on disk.

The design detail that mattered for the migration: there is no separate database process. SQLite is a library, the data is a file, and a single get_db() hands out a connection. Every API handler does conn.execute(sql).fetchall() and reads each row either by column name or by index. That last point turns out to be the hinge the whole rewrite swings on.

The shape of the target

Four decisions, made before any code changed. Postgres instead of SQLite, so a scheduled puller could write to the database over the network while the web pod reads from it. A Gitea repo of its own, rather than leaving the code buried in the blog repo. The cluster’s Gitea registry for the image. And a fresh, read-only IAM user scoped to the one S3 bucket, instead of reusing keys with broader reach.

flowchart LR
  s3[(S3 blog.jeakyl.com-logs<br>CloudFront logs)]
  subgraph ns[k8s namespace: logs]
    cron[CronJob logs-puller<br>every 15 min: download + parse]
    web[Deployment logs-web<br>view.py serve :8765]
    db[(StatefulSet logs-db<br>PostgreSQL, Longhorn PVC)]
  end
  gw[Traefik Gateway<br>192.168.1.31, *.jeakyl.com TLS]
  browser[Browser<br>logs.jeakyl.com] --> gw --> web
  s3 -->|boto3, scoped IAM key| cron --> db
  web --> db
  classDef start fill:#1b2740,color:#fff,stroke:#1b2740;

The web Deployment reads; the CronJob writes every fifteen minutes; both talk to one Postgres StatefulSet on a Longhorn volume. Traefik terminates the wildcard certificate and routes logs.jeakyl.com to the web service. Nothing exotic.

SQLite to Postgres without rewriting the app

This is the part I was quietly dreading, and it turned out to be the easy bit, because of one compatibility accident. SQLite’s Row object lets you read a column either positionally (row[0]) or by name (row["country_code"]), and the code uses both styles everywhere. psycopg2 has no Connection.execute at all, and its default cursor returns plain tuples. Swap the driver naively and every handler breaks.

So I didn’t touch the handlers. I wrote a thin adapter instead: a small PgConn class whose execute opens a DictCursor and hands it back. DictCursor rows support positional access, key access, and dict(row) – exactly the sqlite3.Row surface the code already assumed. get_db() returns one of these. The parser, the GeoIP lookups, the HTTP layer, all the api_* functions: left alone. The whole diff stayed in the database layer, which is where a database migration ought to live.

Then the placeholders. SQLite uses ?, Postgres uses %s, and the bulk insert moved from named :date style to %(date)s. Mechanical, find-and-replace, fine. The DDL swapped INTEGER PRIMARY KEY for SERIAL and REAL for DOUBLE PRECISION. Also fine. And then running it against a real Postgres found the things search-and-replace could not.

The first was a psycopg2 sharp edge. There’s a predicate the dashboard uses to tell real visitors from bots, a run of user_agent NOT LIKE '%bot%' clauses interpolated into several queries. psycopg2 reads % as the start of a parameter marker, so a literal %bot% inside a query that’s executed with a parameters argument makes it choke on %b. You double every literal percent: %%bot%%. Ugly, but it’s what the driver wants.

The second was quieter, and it would have shipped without a sound. SQLite’s LIKE is case-insensitive for ASCII by default; Postgres’s LIKE is case-sensitive. The bot filter matches %bot%, and on SQLite that caught SemrushBot, PetalBot, the lot. On Postgres, LIKE '%bot%' matches bot but sails straight past Bot. Same story for the search box. Nothing errors. The numbers just come out wrong, silently, forever. I changed those clauses to ILIKE to keep the old behaviour, which is the kind of fix you only know you need if you actually look at the output. Which brings me to the third one.

I didn’t trust the migration on a read-through, so before deploying I port-forwarded the cluster’s Postgres to my laptop, ran the puller against it to load real data, and then called every /api/* endpoint in a loop. Eleven came back clean. The twelfth, /api/countries, threw:

column "requests.country_name" must appear in the GROUP BY clause
or be used in an aggregate function

The query selected country_code, country_name, COUNT(*) while grouping only by country_code. SQLite waves that through and picks an arbitrary country_name for the group. Postgres refuses, correctly, because in the general case the answer is ambiguous. Grouping by both columns fixes it, and it’s equivalent here since the name follows from the code. A one-line change to a bug that had been in the original all along, found only because I ran the whole surface against the new database rather than assuming the rewrite was clean.

Three bugs, not one of them caught by the code compiling, all three caught by running it. That’s the case for actually verifying a migration, made in a paragraph.

Containerising, and the manifests

The container is dull, which is the correct state for a container to be in. A python:3.13-slim base, the requirements, view.py and ui.html and the GeoLite2 database copied in, a non-root user, and an entrypoint of python view.py. The default command serves; the CronJob overrides it to run download then parse.

The Kubernetes side comes to four workloads and three secrets. A Postgres StatefulSet on a 5Gi Longhorn volume. A web Deployment pointed at PGHOST=logs-db. An HTTPRoute attaching logs.jeakyl.com to the shared Traefik Gateway. And a CronJob on */15 * * * * with concurrencyPolicy: Forbid, mounting a small persistent volume so the downloaded .gz files survive between runs and the puller doesn’t re-fetch the entire bucket every quarter of an hour. The three secrets – Postgres password, scoped AWS key, registry pull credential – are sops-encrypted in the repo and decrypted only on the way into the cluster.

That scoped IAM user, logs-s3-reader, can do precisely two things: list blog.jeakyl.com-logs and get objects from it. No other bucket, nothing else. If the key ever leaks, the worst case is “someone can read my web server logs”, which isn’t nothing, but it’s a long way from handing over the AWS account.

The pipeline, and where it actually went wrong

I wanted the image built and rolled out by the self-hosted Jenkins that already deploys the blog. The pipeline reads plainly: lint, build the image and push it to the Gitea registry, then on main run kubectl set image against the Deployment and CronJob and wait on the rollout.

flowchart LR
  push[git push main] --> jenkins[Jenkins multibranch]
  jenkins --> lint[lint<br>py_compile]
  lint --> build[build image<br>push to Gitea registry]
  build --> deploy[kubectl set image<br>Deployment + CronJob]
  deploy --> rollout[wait on rollout status]
  classDef start fill:#1b2740,color:#fff,stroke:#1b2740;

Writing that took ten minutes. Getting it to run took rather longer, for three separate reasons, and the last one was my own doing buried two layers down.

The first was registry permissions. Jenkins authenticates to Gitea as a bot user, jenkins-bot, and the image was headed for gitea.jeakyl.com/jean.velloen/logs. The push 401’d. The bot’s token was missing the package scope, so I added it; the push still 401’d. The reason is that jean.velloen is a personal user namespace, and in Gitea only the owning user can push packages there. No collaborator concept exists for a user namespace the way it does for an organisation, so a bot that is a different user could never push to my personal one whatever scopes it carried. I moved the repo into an organisation, JeaKylConsulting, where jenkins-bot is a member, and the push went straight through. The image lives at gitea.jeakyl.com/jeakylconsulting/logs now.

The second was daft and mine. The Jenkinsfile computed the image tag from the git commit in the top-level environment block, with agent none at the top. That block is evaluated before any checkout, so GIT_COMMIT was null, and the declarative pipeline killed the whole build with “One or more variables have some issues with their values: TAG”. Compute the tag in a script step after the checkout, where the commit exists, and it’s fine. Obvious afterwards; invisible until the first run.

The third cost the most and taught me the most. The deploy stage needs a kubeconfig, stored as a Jenkins credential defined in the controller’s Configuration-as-Code YAML. I added the credential block, copied the file across, reloaded Jenkins, and the credential didn’t appear. The deploy failed with “Could not find credentials entry with ID ‘jeakyl-kubeconfig’”.

Two faults were stacked on top of each other. The first was my YAML: I’d written the secret source as a nested ${base64:${readFile:...}}, which the CASC substitutor doesn’t resolve, so the credential quietly failed to construct and got dropped while every other credential loaded around it. The single-function form ${readFileBase64:...} is the right idiom. Fixed.

Fixing it changed nothing, which is when I found the real problem. The Jenkins image bakes its CASC config in and copies it into JENKINS_HOME on first boot, skip-if-exists, and JENKINS_HOME is a persistent volume. So the config file the running controller actually reads was written once, on the very first boot months ago, and no rebuild or recreate had touched it since. Every config change I thought I’d been making had landed on a file nobody read. The earlier GITEA_TOKEN update had worked only because it’s an environment variable resolved at apply time, so it never depended on the file content moving at all.

I proved it from the script console: new File('/var/jenkins_home/casc').canWrite() came back true, and the live config still carried the old seed-job path. The thing was frozen.

The fix is a read-only bind-mount: mount the repo’s jenkins/casc directory straight over the path the controller reads, so the working tree is the live source and a git pull plus a reload is the whole update. After a force-recreate – a plain up -d reported “up-to-date” and skipped the mount, which cost me one more lap – that same script-console check flipped to canWrite: false and the seed-job path matched the repo. The credential loaded. The deploy went green.

With the pipeline clean at last, the rollout did its job: new image, kubectl set image, deployment "logs-web" successfully rolled out. I triggered the puller once by hand to backfill. On an empty volume the first run pulls the whole bucket, so it sat downloading for a few minutes and then parsed the lot in one go: 16,142 rows. The dashboard climbed from the 865 rows of my earlier test to 17,007 total requests, 988 unique visitors, 96.1 MB served, spanning 2026-04-20 to 2026-06-20.

The final check was the one that counts. curl https://logs.jeakyl.com returned HTTP 200 on a valid Let’s Encrypt wildcard certificate, the dashboard HTML, /api/stats answering with live data through the gateway, and plain HTTP redirecting up to HTTPS. It serves itself now. Every fifteen minutes a small pod wakes, pulls whatever CloudFront has dropped into S3, and parses it into Postgres without me anywhere near it.

What I’d carry forward

Two things stuck. The first is that the SQLite-to-Postgres bugs were all latent: the code ran fine for a year because SQLite is permissive, and Postgres simply enforces what SQLite lets slide. Case-insensitive LIKE, an arbitrary column outside GROUP BY – none of it was ever correct, it just hadn’t been punished yet. Changing databases is a free audit of every sloppy query you’ve written, whether you asked for one or not.

The second is that the CASC trap was an infrastructure problem wearing a config problem’s clothes. I spent a good while certain my YAML was wrong, and it was, but fixing it did nothing because the deeper fault was a delivery mechanism that froze the config on first boot. So the rule I’m keeping is to make config live wherever I reasonably can; the bind-mount means the next change is a pull and a reload, not an archaeology dig through a volume.

One loose end I left on purpose. The puller’s first run drags down the whole bucket because the raw cache starts empty, and a smarter version would seed it or lean on --since. I’ve also not put GitOps in front of the manifests; the pipeline rolls the image tag, but the declarative state still goes up by hand. Both are fine for where this sits. The thing I wanted – a log viewer that’s always on and feeds itself – is running, and I can watch exactly who’s reading this from the dashboard it now keeps current.