DEV Community: TABATA Hitoshi

A '12 libraries to make your Python production-ready' list crossed my feed — I use 2 of them, because a framework already made the other 10 decisions

TABATA Hitoshi — Tue, 07 Jul 2026 11:54:38 +0000

A good list came across my feed: 12 libraries that turn a weekend script into
production software — click, python-dotenv, SQLAlchemy, marshmallow, tenacity,
tqdm, schedule, celery, pytest, structlog, the Docker SDK, boto3. Every pick is
defensible. It's the kind of list I'd have loved when I was younger.

I run a production tool — a self-hosted AWS drift detector, live, with real
users. So I checked it against the list, honestly expecting to find gaps.

I use two of the twelve.

Not because I'm hardcore and roll everything by hand. The opposite: because I
picked Django, and Django had already made ten of those decisions before I
wrote a line. That's the part the list doesn't say out loud — choosing a
framework is itself a dependency decision, and it's the one that subsumes most
of the others.

The ten that vanished when I picked the framework

None of these are "I don't need that." They're "I already have that, and adding
the library would give me two of the same thing":

click → manage.py commands. My CLI is Django management commands. Arg parsing, --help, type coercion, discovery — all free, all consistent with the rest of the app. A second CLI framework would just be a parallel one to keep in sync.
SQLAlchemy → the Django ORM. I'm already on one ORM (with psycopg + dj-database-url, so SQLite-to-Postgres is a URL, not a rewrite). Two ORMs in one app isn't flexibility, it's a bug factory — two identity maps, two migration stories, two mental models.
marshmallow → forms + a boundary normalize(). Untrusted input goes through Django form/model validation; the AWS scan results get normalized into a fixed shape at the edge. The validation layer exists; it just isn't a separate library.
schedule / celery → django-apscheduler. I already have periodic scans. And celery specifically is the wrong direction for me right now: I wrote a whole post about guarding a single scheduler from running twice. Bolting on Redis + a distributed task queue is scaling I haven't earned and complexity I'd have to defend at 2am.
structlog's premise ("graduate from print") → I already did. The whole codebase is on stdlib logging with getLogger(__name__). The problem that library solves for a script author — I don't have it.
python-dotenv → compose injects the env. docker compose sets the variables; I read them with os.getenv at the settings boundary. dotenv is a nicety for local non-Docker dev, not a production dependency.
tqdm → server jobs log, they don't draw bars. My scans run on a schedule or a web request, not in an attended terminal. A progress bar has no one to progress to; a structured log line does.
Docker SDK → I'm the thing inside the container. SyncVey ships as a container (docker compose up). It doesn't need to drive Docker from Python. Different job entirely.

That's ten, gone — not by discipline, by a single upstream choice.

The two that survived the filter

Here's where I keep myself honest, because "I need nothing" is always a lie. Two
of the twelve point at real holes Django didn't fill:

tenacity — the genuine gap. boto3 already retries AWS calls for me
(botocore's built-in backoff). But my two non-AWS HTTP calls — posting to a
Slack webhook, fetching the EOL calendar — go out through raw urllib, single
shot, no retry. A blip on either just fails. That's exactly the "transient
network error" case the list names, and the framework doesn't cover it because
it's not a framework concern. This one I should actually add.

structlog — an upgrade, and it did its job before I installed it. I'm on
plain logging, which is fine, but structured/JSON logs would make scan jobs and
attribution failures queryable instead of greppable. And while auditing against
the list I found a stray print(f"Error processing …") that had slipped past my
own logging convention in one view. The list earned its keep not by adding a
dependency — by making me look.

The actual lesson isn't the count

The quote at the bottom of these lists is usually some version of "good
developers write code, great developers assemble systems." True. But assembling
a system doesn't start with adding twelve pieces. It starts with picking the
one piece that pre-answers ten of the questions — and then knowing:

which decisions it already made, so you don't bolt on a second copy (the two-ORM trap), and
which two it left open, so you don't skip them (my un-retried webhook).

The leverage the list is pointing at is real. I'd just locate it one level up:
not in which twelve libraries, but in which one framework makes ten of them
moot — and the discipline to audit the seams it leaves behind.

The honest caveat

If you're writing a genuine standalone script — a CLI tool, a data-munging job,
no web surface, no persistence — the list is dead on and reaching for Django
would be the wrong hammer. click + tqdm + dotenv is exactly right there, and I'd
use all three. The point isn't "framework always wins." It's match the bundle to
the shape of the thing. A weekend script and a hosted service want different
default stacks, and pretending one list fits both is how you end up with two
ORMs.

Takeaways

Picking a framework is a dependency decision — the biggest one. It bundles answers to CLI, ORM, validation, config, logging, and scheduling before you choose any of them individually.
Before adding a library, check whether your framework already gives you that capability. A second one isn't more power; it's a synchronization problem (two ORMs, two CLIs, two config layers).
Then find the seams the framework didn't cover. For me that was retries on non-framework HTTP calls (tenacity) and structured logging (structlog) — the two of twelve that were real.
celery/Redis is scaling you should have to justify, not a default. Single-node with a guarded scheduler is a legitimate production shape.
Auditing your stack against someone else's list is worth it even when you adopt almost none of it — it's how I found a print() I'd left in a view.

The tool I ran this audit against is an open-source, self-hosted AWS drift
detector — Django, MIT, one docker compose up:
syncvey.com. If you checked your own production service
against a "must-have libraries" list, how many would you actually be missing —
and how many did your framework quietly hand you years ago?

Your connection pool is exhausted. pg_stat_activity tells you which of the three causes it is.

TABATA Hitoshi — Mon, 06 Jul 2026 11:36:43 +0000

There's a great genre of article about connection pools — size them (cores × 2), don't make them bigger when they exhaust, put PgBouncer in front so ten app instances don't each open their own twenty connections. All true. All written from the application's side of the glass: your SQLAlchemy pool, your pool_timeout, your worker threads piling into a queue.

The trouble is that from the app's side, pool exhaustion has exactly one symptom, and it's useless. Every request hangs. Pages spin. Eventually: FATAL: too many connections. You go look at the database, braced for a fire, and it's fine — CPU low, queries fast. The database is healthy and your app is dead anyway.

So everyone concludes the database has nothing to tell you. It has plenty to tell you. You were just standing on the wrong side of the glass. pg_stat_activity is the connection log, and it separates the three ways a pool drains into three different shapes.

One query, three fingerprints

The number behind FATAL: too many connections is max_connections. So the first thing you want is the count against it — but broken down by what each connection is doing, because that breakdown is the whole diagnosis:

SELECT count(*) FILTER (WHERE backend_type = 'client backend')                       AS used,
       count(*) FILTER (WHERE backend_type = 'client backend'
                        AND state = 'active')                                        AS active,
       count(*) FILTER (WHERE backend_type = 'client backend'
                        AND state = 'idle')                                          AS idle,
       count(*) FILTER (WHERE backend_type = 'client backend'
                        AND state = 'idle in transaction')                           AS idle_in_txn,
       current_setting('max_connections')::int                                       AS max_conn,
       current_setting('superuser_reserved_connections')::int                        AS reserved
FROM pg_stat_activity;

backend_type = 'client backend' throws out autovacuum, the walwriter, and the other internal processes — they're real backends, but they don't draw on the pool a client competes for, so counting them would just inflate the number that matters. What's left is your applications' connections, sorted into three buckets. The buckets are the tell.

The leak. Code borrows a connection, opens a transaction, and an exception fires before the COMMIT/ROLLBACK — or before the with block that would have returned it. The connection isn't running anything. It's sitting on an open transaction, holding its slot (and any locks it took) forever:

 used | active | idle | idle_in_txn | max_conn | reserved
------+--------+------+-------------+----------+----------
   98 |      2 |    7 |          89 |      100 |        3

89 connections idle in transaction. Nobody is doing any work — active is 2 — and yet you're one connection off the ceiling. That's not load. That's a leak, bleeding one slot per un-returned connection until it hits the wall. idle in transaction pegged near the limit is the single most diagnostic shape in this whole panel.

The slow query. Same ceiling, completely different fingerprint:

 used | active | idle | idle_in_txn | max_conn | reserved
------+--------+------+-------------+----------+----------
   97 |     94 |    2 |           1 |      100 |        3

94 connections genuinely active. Everybody's running SQL — it's just that queries which should take 20ms are taking 2 seconds (an unindexed scan, a lock wait), so each connection is held 100× longer and the pool drains 100× faster under the same traffic. This is the one case where the database really is the bottleneck, and the count tells you so honestly: nothing is leaked, there's just more concurrent work than there are cores to run it.

The pools multiplying. The one that only ever shows up in production:

 used | active | idle | idle_in_txn | max_conn | reserved
------+--------+------+-------------+----------+----------
   96 |      3 |   91 |           2 |      100 |        3

91 connections plain idle. Not in a transaction — just open, doing nothing, warm. This is ten app instances each keeping a "reasonable" pool of twenty connections alive, against a server whose limit is 100. 20 × 10 = 200 wanted, 100 available, and the connections aren't even busy — they're the resting pool, reserved by the app just in case. You never saw it in staging because staging runs one instance. It appears the moment you scale out. idle high while active is near zero is the shape of pools competing for a fixed ceiling.

Three causes the app-side dashboard renders as one identical hang, told apart by which column is large.

Why you get locked out before it's "full"

Notice reserved in that query. superuser_reserved_connections (default 3) holds back slots so a superuser can still get in to fix things when the pool is jammed. The catch: those slots are subtracted from your budget, not added to the top. A normal application login starts failing at max_conn − reserved, i.e. 97, not 100. So the panel that says "97 / 100, you've got headroom" is wrong for everyone who isn't a superuser — they were refused three connections ago. Surface reserved next to the count or you'll misread the last few slots every time.

Naming the connection that's stuck

The counts tell you which failure. To fix a leak you need the specific session — who opened it, from which app, and how long ago they wandered off:

SELECT pid, usename, application_name, state,
       EXTRACT(EPOCH FROM (now() - query_start))::int AS secs,
       query
FROM pg_stat_activity
WHERE backend_type = 'client backend'
ORDER BY (state = 'active') DESC, query_start ASC NULLS LAST;

For an idle in transaction row, query is the last statement it ran before going idle, and secs is how long ago that statement started — so a big secs on an idle-in-transaction row is a transaction somebody opened, ran one thing in, and abandoned. (If you want the strictly precise "time in this state," that's now() − state_change; query_start runs a hair longer because it includes the statement's own runtime, but for spotting the abandoned transaction it's the same story.) Sort active first so live work is on top, then oldest-first so the connection that's been squatting longest floats up. That row is your leak, with a pid you can hand to whoever owns that application_name.

MySQL has the count, not the breakdown

The equivalent on MySQL is Threads_connected against max_connections:

SHOW STATUS LIKE 'Threads_connected';
SHOW VARIABLES LIKE 'max_connections';

You get the ceiling and the headroom — the "too many connections" number — but not the by-state split, because SHOW STATUS doesn't bucket threads into active / idle / idle-in-transaction the way pg_stat_activity does. (You can dig equivalents out of performance_schema, but it's not the one clean counter Postgres hands you.) So the MySQL version answers are you near the wall, but not which of the three walls — you'd fall back to SHOW PROCESSLIST and read states by hand.

The honest part

None of this fixes anything. It's a read-only look at a live view — it can't return a leaked connection or make a slow query fast. The actual repair is on the app's side of the glass, exactly where the pool articles put it: return every connection with with/try-finally, put PgBouncer in front so your instances stop multiplying, size the pool to your cores instead of your fears.

But here's the thing the app's own dashboard structurally cannot do: it only sees its own pool. Instance #4 has no idea instances #1–3 and #5–10 exist, let alone that the ten of them together just overran a 100-connection server. The only place the sum is visible — every instance's connections, every state, side by side against the real ceiling — is pg_stat_activity, on the database's side of the glass. Which of the three failures you have is a question only the database can answer, and it answers it in one query.

This is one piece of cli2ui — a local-only web UI over the psql commands you keep half-remembering. No AI, no SaaS. It's MIT-licensed on GitHub. When your app hangs, do you look at the app, or at pg_stat_activity?

Django's makemessages wrote Japanese I never approved — and my UI shipped it with total confidence

TABATA Hitoshi — Mon, 06 Jul 2026 11:34:12 +0000

I added a drift-history model to my Django app, gave its fields the usual
verbose_names, and ran the one command you always run:

django-admin makemessages -l ja

It found the new strings, dropped them into the .po file, I recompiled, and
moved on. A while later I opened the app in Japanese and a column header read
取得日時 — "fetch time." I never wrote "fetch time." The field is
Detected At. Where did a translation I never typed come from?

makemessages wrote it. And it was confidently, specifically wrong.

Update (2026-07-09). A commenter pushed back on this and was right, so I
went and inspected the actual .mo compiled at that commit. The fuzzy entries
never made it in — compilemessages excluded them exactly as designed. What my
Japanese users really saw was the English msgid, Detected At, sitting in an
otherwise-Japanese page; the 取得日時 was only ever the suggestion in the
.po, caught before anything approved it. So the failure below is the
msgid-fallback one. The "confidently wrong translation ships" failure is
real, but it needs someone to strip the flag first — it isn't what bit me. I've
left the post as written and corrected it here rather than quietly rewriting the
opening. I've also since built the CI gate the last takeaway admits I lacked.

What makemessages actually did

When you add a new msgid that looks similar to one you've already translated,
makemessages doesn't leave it blank. It guesses — it copies the nearest
existing translation over and flags it:

#, fuzzy
#| msgid "Fetched At"
msgid "Detected At"
msgstr "取得日時"

Read that carefully. My new string is "Detected At". gettext looked at my old,
already-translated "Fetched At" → 取得日時, decided the two English strings
were close enough, and pre-filled my new label with the old one's Japanese. The
#| msgid "Fetched At" line is it telling me "I copied this from Fetched At."
The #, fuzzy is it telling me "…but I'm not sure."

It did this across the whole batch:

"Detected At"      取得日時         → should be 検出日時   (bled from "Fetched At")
"Drift Snapshot"   スナップショット   → should be ドリフトスナップショット
"Drift Snapshots"  EOLスナップショット一覧 → should be ドリフトスナップショット一覧  (bled from "EOL Snapshots")

Every one is plausible. 取得 (fetch) and 検出 (detect) are one concept apart.
"Snapshot" really is スナップショット — it just dropped the "Drift" qualifier.
"Drift Snapshots" got the translation for "EOL Snapshots" because both end in
"Snapshots." The heuristic matches on the English source text, and UI labels are
short and repetitive, so it matches things that share words but mean different
things.

Why this is worse than an empty translation

An untranslated string is honest — it falls back to the English source, you see
a stray English word in your Japanese page, you go fix it. A fuzzy string is
a liar wearing a suit. There are two ways it bites, and both are quiet:

Django's compilemessages drops fuzzy entries by default. So the "safe" outcome is that your brand-new labels silently revert to their English msgid in the middle of a Japanese UI — translation present in the .po, absent in the running app.
The moment anyone accepts the guess — strips the #, fuzzy line, runs msgfmt --use-fuzzy, or lets a translation tool auto-approve — the wrong Japanese ships and looks completely intentional.

And here's the part that made it invisible to me: my app defaults to English
(LANGUAGE_CODE = 'en') and only switches to Japanese from the browser's
Accept-Language via LocaleMiddleware. I review in English. Every reviewer
who reads the diff in English sees a green .po full of confident-looking
Japanese and no reason to doubt it. The only people who see 取得日時 where it
should say 検出日時 are the Japanese users — who assume that's just what the app
calls it.

The fix is a discipline, not a command

There's no clever flag. The fix is to treat #, fuzzy as "unreviewed," never
"translated." After every makemessages:

grep -n "#, fuzzy" locale/ja/LC_MESSAGES/django.po

For each hit, read the #| msgid "…" line — it tells you exactly which old
string the guess bled from — then either correct the msgstr and delete both
comment lines, or clear it and translate from scratch. Only then recompile:

django-admin compilemessages

Same sweep surfaced two neighbors of the same bug: a string with an empty
msgstr ("More actions" → falling back to English) and a msgstr that had
been concatenated onto itself — the same Japanese sentence pasted twice into
one message. Both are the kind of thing you never notice reviewing in your source
language.

Takeaways

makemessages doesn't leave new strings blank — it guesses from your existing translations and marks the guess #, fuzzy. That's a suggestion, not a translation.
The guess matches on source-text similarity, so short, repetitive UI labels (verbose_names especially) are exactly where it's most likely to be wrong — "Detected At" inherits "Fetched At," "Drift Snapshots" inherits "EOL Snapshots."
compilemessages ignores fuzzy by default, so a fuzzy string either silently reverts to English or, if anyone accepts it, ships a confident mistranslation. Both are quiet failures.
If you review in your source language, you are structurally blind to this. The bug only renders in the target locale.
Make grep "#, fuzzy" (and empty msgstr) part of the ritual — better, make it a CI check that fails the build when either exists. I've since built exactly that: a stdlib-only checker that walks the .po, flags any surviving fuzzy entry (reporting the old string the guess bled from) or empty msgstr, and exits non-zero. The gate has to be enforced, not remembered. The .mo is still hand-compiled and committed, so a stale one can drift from the .po — that one I haven't closed.

This is one paper cut from a self-hosted AWS drift detector I build in the open —
Django, bilingual UI, MIT, one docker compose up:
syncvey.com. If you ship a translated app: do you have
anything that stops a #, fuzzy from reaching production, or do you catch the
wrong ones the way I did — a user squinting at a label that isn't quite right?

Order your tables for a safe TRUNCATE with graphlib — circular FKs come free

TABATA Hitoshi — Sun, 05 Jul 2026 11:51:25 +0000

I wanted a panel that answers one boring, dangerous question: in what order can I empty these tables without a foreign key stopping me?

If orders references customers, you can't TRUNCATE customers first — a row in orders still points at it. You empty the children before the parents. With three tables you do this in your head. With forty, laced with a many-to-many join table and one employees.manager_id that points back at employees, you do it wrong, get ERROR: update or delete on table "…" violates foreign key constraint, and start bisecting by hand.

That order has a name — it's a topological sort of the foreign-key graph — and I was about to write the visited/back-edge walk from memory when I remembered Python has shipped one since 3.9. graphlib. Nobody imports it. It's perfect for exactly this.

The whole ordering is one stdlib call

Model each foreign key as an edge: the child (the table holding the FK) depends on the parent (the table it references). Feed those dependencies to graphlib.TopologicalSorter and ask for a static order:

from graphlib import TopologicalSorter, CycleError

def build(tables, edges):
    sorter = TopologicalSorter()
    for t in tables:
        sorter.add(t)                    # add every table, so isolated ones appear
    for e in edges:
        if e.child == e.parent:
            continue                     # self-reference — handled below
        sorter.add(e.child, e.parent)    # child depends on parent
    load_order = list(sorter.static_order())   # parents first
    return {
        "load_order": load_order,               # safe INSERT / restore order
        "truncate_order": list(reversed(load_order)),  # safe TRUNCATE / DELETE order
    }

static_order() yields dependencies before the things that depend on them, so you get parents first — that's the order you'd load data in (or restore a dump). The safe order to empty tables is the exact reverse: children first. One reversed() and you have both directions from the same sort.

Two small but load-bearing details:

Add every table before adding edges. A table with no foreign key in or out never appears in an edge, so if you only add edges it silently drops off your list. sorter.add(t) with no predecessors registers it as a free-floating node that still comes out in the order.

The edge direction is the thing to get right. sorter.add(child, parent) reads as "child depends on parent." Flip it and your TRUNCATE order confidently tells you to empty the parent first — the exact mistake the panel exists to prevent. Worth a comment at the call site.

The cycle detection is the part you didn't write

Here's the payoff. Some schemas have circular foreign keys — a references b and b references a (usually via a nullable column you're meant to fill in a second pass). When that happens no valid order exists, and a hand-rolled topological sort's failure mode is to either loop forever or silently emit a partial, wrong answer.

TopologicalSorter refuses to guess. It raises:

try:
    load_order = list(sorter.static_order())
    cycle = []
except CycleError as exc:
    # CycleError.args == (message, [n1, n2, …, n1]) — the second item is the
    # node list of the cycle, first element repeated at the end.
    load_order = []
    cycle = list(exc.args[1]) if len(exc.args) > 1 else []

CycleError doesn't just tell you a cycle exists — exc.args[1] is the actual list of tables caught in it, with the first repeated at the end so you can read the loop. So the same call that computes the order also detects the one schema footgun that makes the order impossible, and names the tables involved, with zero extra code. You get a real diagnostic for free out of an exception you were catching anyway.

The self-reference trap

employees.manager_id → employees is a foreign key from a table to itself. If you hand that to the sorter as add("employees", "employees"), you've declared that employees is its own predecessor, and it trips a false CycleError — a one-node "cycle" that isn't the kind you care about.

But you can't just drop self-references either, because they're real — they just constrain a different level. A self-referential FK doesn't stop you from emptying the table in one shot: a single-table TRUNCATE employees (or DELETE FROM employees with no WHERE) removes every row at once and the constraint is satisfied. Where it bites is row by row — you can't delete one manager while a subordinate row still points at it, so a partial delete has to walk the table in dependency order. So the honest move is to exclude self-references from the table-level ordering and report them separately:

self_refs = sorted({e.child for e in edges if e.child == e.parent})

They don't constrain table-level order — every row's parent is in the same table — but they're worth surfacing as their own note, not swept into the graph where they'd manufacture a phantom cycle.

Getting the edges: one catalog query

The graph is engine-agnostic once you have the edges; only the extraction is Postgres-specific. Foreign keys live in pg_constraint where contype = 'f', with conrelid = the child and confrelid = the parent:

SELECT con.conname,
       ns.nspname  || '.' || cl.relname  AS child,
       fns.nspname || '.' || fcl.relname AS parent
FROM pg_catalog.pg_constraint con
JOIN pg_catalog.pg_class cl      ON cl.oid = con.conrelid
JOIN pg_catalog.pg_namespace ns  ON ns.oid = cl.relnamespace
JOIN pg_catalog.pg_class fcl     ON fcl.oid = con.confrelid
JOIN pg_catalog.pg_namespace fns ON fns.oid = fcl.relnamespace
WHERE con.contype = 'f'
  AND ns.nspname  NOT IN ('pg_catalog', 'information_schema')
  AND fns.nspname NOT IN ('pg_catalog', 'information_schema')
ORDER BY child, con.conname;

Rows in, ForeignKeyEdge(child, parent) out, straight into the builder above. The graph logic never learns which database it came from.

The same shape in MySQL

MySQL keeps the same relationship in information_schema.KEY_COLUMN_USAGE — a foreign-key row is one where REFERENCED_TABLE_NAME is not null:

SELECT kcu.CONSTRAINT_NAME,
       CONCAT(kcu.TABLE_SCHEMA, '.', kcu.TABLE_NAME)                       AS child,
       CONCAT(kcu.REFERENCED_TABLE_SCHEMA, '.', kcu.REFERENCED_TABLE_NAME) AS parent
FROM information_schema.KEY_COLUMN_USAGE kcu
WHERE kcu.TABLE_SCHEMA = ?
  AND kcu.REFERENCED_TABLE_NAME IS NOT NULL
GROUP BY kcu.CONSTRAINT_NAME, kcu.TABLE_SCHEMA, kcu.TABLE_NAME,
         kcu.REFERENCED_TABLE_SCHEMA, kcu.REFERENCED_TABLE_NAME;

Same two columns, same edge, same TopologicalSorter. The graphlib half doesn't change one line — swapping the query is the entire port. That's the nice thing about pushing all the database-specific knowledge into "produce a list of (child, parent) pairs" and letting the standard library own the graph.

The honest part

This computes and shows the order — it doesn't run a TRUNCATE. That's deliberate: the value is answering "what's the safe order, and do you even have a valid one?" without touching a byte, and a few caveats keep it honest. A DEFERRABLE constraint can be legally violated mid-transaction, so a cycle that looks fatal here may be resolvable with SET CONSTRAINTS … DEFERRED. Composite and multi-column FKs are still just one edge between two tables. And the topological order is one valid order, not the only one — independent subgraphs can be interleaved however you like.

But the core is solid and it's almost free: the shortest correct path from "a pile of foreign keys" to "a safe delete order, plus a named list of the circular ones" is a for loop and a try/except around a standard-library class most people have never imported. You were about to write the graph algorithm. You didn't have to.

This is one piece of cli2ui — a local-only web UI over the psql commands you keep half-remembering. No AI, no SaaS. It's MIT-licensed on GitHub. What's the last stdlib module you reimplemented before realizing it already existed?

My drift detector graded every change — and stayed blind to the secret that hadn't rotated in 200 days

TABATA Hitoshi — Sun, 05 Jul 2026 11:48:01 +0000

My drift detector is built on one idea: take two snapshots of your cloud, diff
them, and grade what moved. A security group opened to 0.0.0.0/0? Critical. An
RDS instance that flipped to public? Critical. A tag someone fat-fingered? Low.
Every rule is a function of an old → new transition — a change happened, and
I score how bad the change is.

Then I went to add "your Secrets Manager secret hasn't rotated in too long" and
the whole model fell over. Not because it's hard to detect. Because the thing I
wanted to flag produces no diff.

The shape that breaks a diff

Here's a secret that hasn't rotated in 200 days. I scan it Monday. I scan it
Tuesday. I diff the two snapshots:

(no changes)

Of course there are no changes — nothing rotated. That's the entire problem.
Scan it every minute for a week and every diff comes back empty. The dangerous
state of this secret is precisely the state in which nothing is happening to
it.

My whole engine was wired to answer "what changed between two points in time?"
This risk lives in the opposite question: "what is true about this thing right
now, regardless of whether it just changed?" A password that rotated 200 days
ago and a password that rotated 201 days ago look identical to a diff — but one
of them crossed my 90-day line and the other didn't, and only absolute state
knows that.

I'd quietly assumed every risk was a change risk. It isn't. Some risks are
standing conditions: overdue rotation, public-by-default, encryption never
enabled. The absence of a change is the finding.

Two different questions, two different modules

So I stopped trying to force rotation through the diff path. The change-based
rules stayed exactly as they were — they need a prior snapshot and grade the
transition:

# rules.py — grades a field-level diff (old → new)
if asset.raw_data_prev:
    changes = _compute_raw_diff(asset.raw_data_prev, asset.raw_data)
    if changes:
        findings.extend(assess(asset.asset_type, changes)['findings'])
        has_change = True

Rotation got its own module that grades current state, with no _prev in
sight:

# rotation.py — grades a standing condition (now, no diff)
if (asset.raw_data or {}).get('_resource_type') == 'aws_secretsmanager_secret':
    findings.extend(assess_rotation(asset.raw_data, now, max_age)['findings'])

Both emit the same {'field', 'severity', 'reason'} shape, so the two streams
of findings merge into one row and sort into the same severity-ranked list. The
UI never knows one came from a diff and the other from a stopwatch.

What the grader actually looks at

Rotation posture isn't one boolean, it's a little ladder, and each rung is a
different severity:

def assess_rotation(raw_data, now, max_age_days):
    raw = raw_data or {}
    findings = []

    if not _truthy(raw.get('rotation_enabled')):
        findings.append({'field': 'rotation_enabled', 'severity': HIGH,
                         'reason': 'Automatic rotation is disabled'})
        return _wrap(findings)

    last = _parse(raw.get('last_rotated_date'))
    if last is None:
        findings.append({'field': 'last_rotated_date', 'severity': MEDIUM,
                         'reason': 'Rotation is enabled but the secret has never rotated'})
        return _wrap(findings)

    age_days = (now - last).days
    if age_days >= max_age_days * 2:
        findings.append({'severity': CRITICAL, ...})   # over 2× the limit
    elif age_days >= max_age_days:
        findings.append({'severity': HIGH, ...})        # past the limit
    return _wrap(findings)

Rotation disabled → HIGH. It's not overdue, it's structurally never coming. Worse than "late."
Enabled but never rotated → MEDIUM. Someone flipped the switch and walked away; the Lambda may be misconfigured.
Overdue past the limit → HIGH.
Over twice the limit → CRITICAL. 90 days late is a mistake; 180 days late is a dead process nobody's watching.

It's a pure function over a dict — no AWS calls, no models, fully testable with a
frozen now. Same discipline as the change rules: keep the judgement pure, keep
the I/O outside.

The scan captures posture, never the secret

The one thing I was paranoid about: a tool that reads your secrets to check
your secrets is a worse problem than the one it solves. It never touches a
value.

ListSecrets already returns the rotation metadata — RotationEnabled,
LastRotatedDate, NextRotationDate — so there's no GetSecretValue, not even
a DescribeSecret. One list call carries everything the grader needs:

sm.get_paginator('list_secrets')
# → RotationEnabled, LastRotatedDate, NextRotationDate, RotationRules ...
# never GetSecretValue. posture only, value never leaves AWS.

I capture whether it rotates, when it last did, and how often it's supposed to —
and nothing that would be dangerous to store. There's a moto-backed test whose
entire job is to assert the scanned record never contains the secret string.

The detail I nearly got wrong: "Who changed this?"

Every risk row has a lazy "Who changed this?" button — click it and the tool
calls CloudTrail to name whoever made the change (I wrote about that
in Day 11).

For an overdue rotation, that button is a lie. There is no actor. Nobody did
anything — that's the whole finding. Asking CloudTrail "who caused this secret to
not rotate?" returns nothing, because non-events don't have culprits.

So the button is gated on the same has_change flag that the diff path sets:

findings.extend(assess(asset.asset_type, changes)['findings'])
has_change = True   # only change-based findings get an actor

A row that exists only because of a standing condition renders without the
attribution button. The shape of the risk decides whether "who did it?" is even a
coherent question — and for the absence of an event, it isn't.

Honest limits

The severity ladder is a heuristic. A 90-day default and a 2× critical cliff are opinions, not policy — hence the SECRET_ROTATION_MAX_AGE_DAYS setting.
It grades what ListSecrets reports. A rotation Lambda that "succeeds" while silently rotating to the same value would still look healthy. Posture, not proof.
Secrets Manager only. Rotation-as-a-standing-condition generalizes (IAM access keys, TLS certs, KMS key age) but I've only wired the one so far.

Takeaways

Not every risk is a change risk. Diff-based detection is structurally blind to conditions that are dangerous precisely because nothing is changing — an overdue rotation produces no old→new transition to grade.
When a new risk doesn't fit your existing pipeline, that's a signal it's a different question, not a harder version of the same one. Give it its own path instead of bending the diff to fit.
Grade standing conditions on absolute current state (now vs last_rotated_date), not on a snapshot delta.
Check posture without reading the secret — ListSecrets carries the rotation metadata, so you never call GetSecretValue.
Attribution only makes sense for events. Gate "who did this?" on whether a discrete change actually happened; non-events have no culprit.

This ships in a self-hosted tool that scans your live AWS, grades what drifted
and what's standing overdue, and never stores a secret value — open source
(MIT), one docker compose up: syncvey.com. What's the
most dangerous thing in your account right now that would never show up in a
diff because it's been quietly not changing for months?

The Terraform MCP server has 35+ tools and none of them can see the resource I made in the console

TABATA Hitoshi — Fri, 03 Jul 2026 11:25:41 +0000

Everything is getting an MCP server, and Terraform is no exception — HashiCorp
ships an official one now. Point your AI at it and you get 35+ tools: search the
provider registry, pull module inputs and examples, look up Sentinel policies,
list and drive HCP Terraform workspaces, runs, and variables. As an
Infrastructure-as-Code copilot it's genuinely great.

I build a drift detector, so I asked it the one question I always ask any new
Terraform tool: can you find the security group my coworker opened by hand in
the AWS console last Tuesday?

No. And — this is the interesting part — it structurally can't, for the exact
same boring reason terraform plan can't. A shiny AI wrapper doesn't patch the
hole; it inherits it.

What the Terraform MCP server actually sees

Look at what its tools are grouped into: provider docs, module registry
metadata, policy (Sentinel), and HCP Terraform / Enterprise workspace
management — runs, variables, tags, stacks. Notice what's not in that list:
there is no scan_aws_account, no list_untracked_resources, nothing that
reaches into a live cloud and enumerates what's really there.

When anything in that ecosystem "detects drift," it bottoms out at the same
command it always has:

terraform plan -refresh-only

Refresh reconciles your state file against the real world. For every resource
already in state, it re-reads the live attributes and shows you what moved.
That's the whole mechanism — and the whole limitation.

The blind spot is the data source, not the interface

Here's the part that no amount of AI changes. terraform plan starts from
state and asks: "do the things I already know about still match reality?"

So the resources it can tell you about are exactly the resources Terraform put
there. The security group someone created in the console has no state entry.
There's no row to refresh, nothing to compare, so plan — and by extension the
MCP server orchestrating it — reports a clean, confident "No changes."

The most dangerous drift isn't a managed resource that wandered. It's the
resource that was never managed at all: the emergency S3 bucket, the
click-ops RDS instance, the SG opened to 0.0.0.0/0 at 2am during an incident.
State-based tooling is blind to all of it by construction, because you can't
refresh a row that doesn't exist.

Start from the cloud, not from the state

My tool asks the opposite question — not "does my state still match?" but
"what is actually in this account?" — by listing the account directly with
boto3:

def scan_ec2(session):
    ec2 = session.client('ec2')
    for page in ec2.get_paginator('describe_instances').paginate():
        for reservation in page['Reservations']:
            for inst in reservation['Instances']:
                yield normalize(inst)   # everything AWS has, not what tf knows

There's one of these per service — EC2, S3, security groups, RDS, and so on —
each just enumerating what the API says is live. Then it diffs that against the
imported tfstate. A resource that shows up in the scan with no matching state
entry is the thing terraform plan will never mention: real, running, and
unmanaged.

The difference is entirely in where you begin. State-first tools can only ever
describe the map they were handed. Cloud-first tools describe the territory.

To be fair to the MCP server

None of this is a knock on the Terraform MCP server — it's aimed at a different
job, and it's good at it. Authoring config against current provider docs,
discovering modules, wiring up HCP workspaces and runs, checking policy: those
are real wins, and having an AI do them against live registry data instead of
its stale training memory is exactly right.

It just isn't a cloud-discovery tool, and "add an MCP server" doesn't turn a
state-based engine into one. If your worry is config authoring, reach for it.
If your worry is what did someone spin up behind Terraform's back, you need
something that queries the cloud, not the state.

Takeaways

The official Terraform MCP server is a registry + HCP-orchestration copilot. Its 35+ tools do not include live cloud discovery.
Any drift it surfaces still runs through terraform plan -refresh-only, which only reconciles resources already in state.
Console-made ("click-ops") resources have no state row, so state-based tooling — CLI or MCP — is blind to them by construction. This is a data-source limit, not a UX one.
To catch unmanaged resources you have to invert the question: list the cloud directly and diff that against state, not the other way around.

This is the core of a self-hosted tool that scans your live AWS and flags what
Terraform never planted — open source (MIT), one docker compose up:
syncvey.com. When a resource appears in your account that
nobody wrote HCL for, what catches it today — a scheduled scanner, a CSPM tool,
or an angry bill at the end of the month?

Your database GUI is a text editor. Where's the console?

TABATA Hitoshi — Fri, 03 Jul 2026 11:24:31 +0000

I spent an evening going through a big directory of database GUIs — the Macs-and-menu-bars kind, forty-something of them. DBeaver, TablePlus, Beekeeper, Postico, DataGrip, a wall of Redis and Mongo clients, a couple of new AI-first ones. Good tools. Genuinely nice to use.

And somewhere around tool number thirty it clicked that almost every single one is the same shape: a connection sidebar, a SQL editor, and a data grid you can sort and filter and edit cells in. That's the archetype. It's a text editor for your database, with a spreadsheet stapled to the results.

Which is great — right up until the question you have isn't "let me write a query."

The questions an editor is bad at

Here's the thing I keep hitting, and I don't think I'm alone. The moments I actually open a database tool in anger are almost never "I want to author a SELECT." They're operational:

Something is hung. Who is blocking whom?
The app is throwing connection errors. Am I out of connections, and how close was I?
A table feels bloated. Is autovacuum keeping up, or has it not run in three weeks?
This index — is anything even using it, or am I paying to maintain dead weight?

Every one of those has a real answer sitting in a system view. pg_locks joined to pg_stat_activity. count(*) from pg_stat_activity against max_connections. n_dead_tup and last_autovacuum in pg_stat_user_tables. idx_scan = 0 in pg_stat_user_indexes.

And here's how a query-editor tool answers them: it hands you the editor. So you paste in the pg_stat_activity join you half-remember, get a column name wrong, look it up, run it, squint at the raw rows, and mentally reconstruct the blocking graph yourself. The tool didn't answer the question. It gave you a place to type the question, again, for the hundredth time.

That's the hole. The category optimized hard for authoring SQL and left reading the state of the database as an exercise for the user.

Editor questions vs. console questions

It's worth being precise about the split, because it's not "some tools are better." It's two different jobs:

Editor question	Console question
What rows match this filter?	Who is blocking whom right now?
Let me edit this cell.	How many connections until I hit the ceiling?
Design this schema.	Has autovacuum touched this table lately?
Format and save this query.	Which indexes are dead weight?

A SQL editor is the right tool for the left column. It is a genuinely bad fit for the right column — not because it can't run the query, but because "run the query and read raw rows" is the entire user experience it offers, and the right-column questions want a computed answer, not a result set.

The tell is that the operational answer is almost always a join across two or three catalog views plus a derived number — the blocking graph, the headroom percentage, the dead-tuple ratio. That's not something you want to retype. That's something you want a tool to have already turned into a line that says "session 4823 is blocking two others" or "you're at 71% of max_connections."

The one instinct that is spreading

There was one exception in the pile worth calling out, and it's a hopeful one. A couple of the newer clients lead with read-only by default — you connect, and the tool won't let you mutate anything unless you opt in. One AI-first Postgres client puts that right in its headline.

I found that genuinely encouraging, because it's the same instinct from a different direction: the tool taking responsibility for the state of the database instead of treating it as a dumb text target. Read-only-by-default says "I assume you're here to look, not to break things." That's a console instinct leaking into the editor category, and I'll take it.

(It's also the exact reason my own runner defaults to SET TRANSACTION READ ONLY at the server, not by parsing your SQL — but that's a whole other post.)

So build the console

None of this is a knock on the editors. If your job is writing and iterating on SQL all day, a great editor is the correct tool and you should use the nicest one you can find.

But there's a second job — reading the live state of a database and catching the thing about to fall over — and the market answers it, overwhelmingly, by handing you an editor and wishing you luck. The interesting product isn't a prettier editor. It's the tool that has already run the pg_stat join for you and put the answer on the screen: the blocking graph drawn as a graph, the connection headroom as a number with a ceiling, the unused indexes listed honestly, autovacuum's last pass with a timestamp you can actually read.

That's the shape of an operations console, and it's a strangely empty shelf. Which, after forty tools, is the part I keep thinking about.

This is one piece of cli2ui — a local-only web UI over the psql commands you keep half-remembering, built to answer the console questions above without making you retype the pg_stat join. No AI, no SaaS. MIT-licensed on GitHub. What's the operational question you keep pasting into a query editor?

My infra tool knew what drifted, how bad, and who did it — then waited for someone to open a tab

TABATA Hitoshi — Wed, 01 Jul 2026 10:51:12 +0000

Over the last few weeks I taught my self-hosted drift detector some genuinely
useful tricks: it keeps a history so you can see the trend, it grades
each drift by security impact, and it can tell you who made the change via
CloudTrail. On paper, that's exactly what you want when infrastructure quietly
diverges from Terraform.

There was one problem, and it took me embarrassingly long to name it: all of
it was pull. You had to remember the tool existed, open it, and go look. On a
random Tuesday afternoon, nobody does. A signal nobody pulls is a signal that
doesn't exist.

So I made it push. A Monday-morning briefing that lands in Slack on its own.

What the briefing says

The whole point is synthesis — one message that answers what changed, how bad,
which way it's trending, and who to talk to:

Drift briefing — Production
3 drifted  ·  ▲ 2 since last week
🔴 1 critical  🟠 1 high  🟡 1 medium  ⚪ 0 low
─────────────
CRITICAL  web-sg (SG / prod)
Opened to the entire internet (0.0.0.0/0)
changed by tanaka via AuthorizeSecurityGroupIngress

None of that is new data. It's the drift history, the severity rules, and the
CloudTrail lookup I already had — assembled and delivered instead of sitting in
a tab. The building was easy. Realizing it needed to come to people was the
part I'd missed.

Rule one: shut up on a quiet week

The fastest way to get a notification muted is to send it when nothing happened.
So the briefing only fires if there's something to say:

digest = build_digest(system, days=days, attribute=True)
if not digest['has_data']:
    return False        # a clean week shouldn't ping anyone
return _post_to_slack(url, _format_slack(digest))

No drift, no message. The absence of a Monday briefing is the good news.

The interesting part: a plugin that schedules its own job

Here's the architecture constraint I'd set myself earlier in this project: the
risk/attribution/digest stuff lives in an optional, detachable app, and the
core is not allowed to import it. Remove the app from INSTALLED_APPS and the
core should neither know nor care.

That's easy for a web view (route it, guard it with a flag). It's harder for a
background job. The scheduler lives in the core. How does the core register
a cron job that belongs to a plugin it's forbidden to import?

The answer is the same trick Django itself uses for a lot of things: discovery,
not import. The core asks every installed app "do you have any jobs for me?"
without knowing which apps those are:

# core: collect job specs from any plugin that offers them
def plugin_scheduled_jobs():
    jobs = []
    for cfg in _plugin_app_configs():          # apps with syncvey_plugin = True
        getter = getattr(cfg, 'scheduled_jobs', None)
        if not callable(getter):
            continue
        try:
            jobs.extend(getter() or [])
        except Exception:                      # a bad plugin must not block startup
            continue
    return jobs

The core scheduler just iterates whatever comes back:

from .plugins import plugin_scheduled_jobs
for spec in plugin_scheduled_jobs():
    _scheduler.add_job(
        spec['func'], trigger=spec['trigger'], id=spec['id'],
        name=spec.get('name', spec['id']), jobstore='default',
        max_instances=1, coalesce=True, replace_existing=True,
    )

And the plugin advertises its job from its AppConfig — importing apscheduler
and its own code lazily, so the core never pulls any of it in:

class DriftRiskConfig(AppConfig):
    syncvey_plugin = True

    def scheduled_jobs(self):
        from django.conf import settings
        if not getattr(settings, 'DRIFT_DIGEST_ENABLED', False):
            return []                          # opt-in; silent by default
        from apscheduler.triggers.cron import CronTrigger
        from .digest import run_digest_job
        return [{
            'id': 'drift_digest_weekly',
            'name': 'Weekly drift briefing',
            'func': run_digest_job,
            'trigger': CronTrigger(day_of_week='mon', hour=9, minute=0),
        }]

Now the core scheduler runs a job it has never heard of, and deleting the plugin
deletes the job with it. No if plugin_installed: branches in the core, no
import, no coupling.

The gotcha that bit me: jobs are stored by reference

I use django-apscheduler, which persists jobs in the database so they survive
a restart. That persistence is the trap. apscheduler doesn't pickle your
function — it stores it by import path (syncvey_drift_risk.digest:run_digest_job)
and re-imports it when the job fires.

My first version made run_digest_job a closure inside scheduled_jobs(),
because it needed a bit of context. apscheduler couldn't serialize it: "cannot
be scheduled, it is not importable." The fix is boring but absolute — the job
has to be a module-level function anything can import by name. Any state it
needs, it looks up itself when it runs (it just iterates the systems that have a
Slack webhook). Once the job is a plain top-level function, the persistent store
is happy.

The opt-in flag matters for the same reason a clean week stays silent: a fresh
docker compose up shouldn't start firing outbound Slack messages. scheduled_jobs()
returns nothing until you set DRIFT_DIGEST_ENABLED=true and configure a webhook.

Takeaways

A monitoring feature you have to remember to open isn't done. Pull tells you nothing at 2pm on a Tuesday; push meets people where they already are.
Send nothing on a quiet week. The most trustworthy alert channel is the one that only speaks when it matters.
To let an optional plugin extend the core — even with a background job — use discovery (getattr(app_config, 'hook', None)), not import. The core asks; it never names the plugin.
If your scheduler persists jobs (django-apscheduler and friends), the job must be a top-level importable function — it's stored by path, not pickled. Closures and bound methods will fail at serialization time.

This is the weekly briefing from a self-hosted tool that tracks how your live AWS
drifts from Terraform — open source (MIT), one docker compose up:
syncvey.com. Where do your infra alerts actually land —
Slack, email, a dashboard you open on purpose, or a channel everyone muted months
ago?

My drift detector knew a security group changed — not that it was dangerous, or who opened it

TABATA Hitoshi — Mon, 29 Jun 2026 12:12:15 +0000

My tool detects Terraform drift: it scans live AWS, diffs it against tfstate,
and lists every resource that no longer matches. For a long time I thought that
was the whole job.

Then I was staring at a real drift report — forty-odd changes — during a "wait,
why is that open?" moment, and I realized the report was answering the wrong
question. It told me what changed. It couldn't tell me the two things I
actually needed in that moment:

Which of these forty is an emergency, and which is noise?
Who changed it?

A flat list where a security group opened to the entire internet sits in the
same grey row as a renamed tag is a list that makes you do the triage. So I
fixed both. Here's how, and the bits that bit me.

Part 1 — Not all drift is equal

terraform plan is deliberately value-neutral: a diff is a diff. But to a human
on call, 0.0.0.0/0 appearing in an ingress rule is a heart-attack, and
Name: web → Name: web-1 is a shrug. The tool already had the field-level
diff — it just treated every field the same.

So I graded each change. No new AWS calls, no model — pure logic over the diff I
already compute:

def classify_change(asset_type, field, old, new):
    f     = (field or '').lower()
    old_s = str(old or '').lower()
    new_s = str(new or '').lower()

    # opened to the world
    if '0.0.0.0/0' in new_s and '0.0.0.0/0' not in old_s:
        return CRITICAL, _('Opened to the entire internet (0.0.0.0/0)')
    if 'public' in f and new_s in _TRUTHY and old_s not in _TRUTHY:
        return CRITICAL, _('Resource was made publicly accessible')

    # protections removed
    if any(k in f for k in ('encrypt', 'kms', 'sse')) and old_s not in _FALSY and new_s in _FALSY:
        return HIGH, _('Encryption was disabled')
    if any(k in f for k in ('policy', 'iam', 'role', 'principal', 'acl')):
        return HIGH, _('Access or permission configuration changed')

    return LOW, _('Configuration value changed')

A resource's severity is just the worst of its field changes — open a port and
rename a tag, you're an incident, not a shrug:

worst = LOW
for c in changes:
    sev, reason = classify_change(asset_type, c['field'], c['old'], c['new'])
    if SEVERITY_ORDER[sev] > SEVERITY_ORDER[worst]:
        worst = sev

Two things I'd flag if you copy this. First, grade on the transition, not the
value — 0.0.0.0/0 in new and not in old only fires when the change opened
it; a group that was always public doesn't scream every scan. Second, these are
heuristics, not a policy engine — string-matching field names will miss
things and occasionally over-flag. That's a deliberate trade: a fast, obvious
"this one first" beats a correct-but-unshipped OPA integration. I'd rather be
roughly right on every resource today.

Part 2 — "...but who did this?"

Severity tells you which drift to open first. It still doesn't tell you who to go
talk to. And terraform plan structurally cannot tell you — it compares two
files; it has no idea a human touched the console at 3pm.

But my tool isn't stateless. It already assumes a read-only role into each
account to scan it. Which means CloudTrail is right there, one API call away:

_READ_PREFIXES = ('Describe', 'List', 'Get', 'Lookup', 'BatchGet')

def lookup_actor(session, resource_id):
    try:
        client = session.client('cloudtrail')
        resp = client.lookup_events(
            LookupAttributes=[{'AttributeKey': 'ResourceName',
                               'AttributeValue': resource_id}],
            MaxResults=10,
        )
    except Exception as exc:          # AccessDenied, throttling, no trail...
        logger.warning('CloudTrail lookup failed for %s: %s', resource_id, exc)
        return None                   # attribution is a bonus, never load-bearing

    for ev in resp.get('Events', []):
        if ev['EventName'].startswith(_READ_PREFIXES):
            continue                  # skip the Describe/List noise — we scan a lot
        return _parse_event(ev)       # who / when / source IP, from the event JSON
    return None

Three things that bit me here:

Filter out read events. My first version proudly reported that the last thing to touch the security group was... my own scanner, calling DescribeSecurityGroups. The tool kept catching itself. Skipping the Describe/List/Get prefixes gets you the actual mutating event.
It must never break the page. LookupEvents can be denied (missing permission), throttled, or simply find nothing. Every one of those returns None and the row says "no record" — attribution failing can't take down the drift report.
Do it lazily. Calling CloudTrail for forty resources on page load is slow and rate-limit roulette. So it's a per-row "Who changed this?" button — CloudTrail is only hit for the one resource you actually care about.

I also added cloudtrail:LookupEvents to the bundled IAM policy, and I'm honest
in the UI about the limits: CloudTrail Lookup covers ~90 days of management
events, and it's regional. Sometimes the answer is "no record," and that's fine —
it's still more than terraform plan ever offered.

Where it lives: a detachable plugin

I didn't grow this inside the core app. It's its own optional Django app that
plugs in through one seam — a feature flag plus a sidebar entry — and the core
never imports it. Drop it from INSTALLED_APPS and the nav entry disappears and
the routes 404; nothing else notices. Keeping advanced features at arm's length
like this means the core stays a clean, boring ledger, and the interesting stuff
is opt-in. (That's a whole post of its own.)

Takeaways

Drift detection is the easy 80%. The decision-useful part is how bad and who — and neither comes from diffing two files.
Grade severity on the transition (old→new), not the current value, or your dashboard cries wolf on every scan.
Heuristic severity that ships beats a perfect policy engine that doesn't. You can always tighten the rules later.
If you already hold credentials into an account, CloudTrail attribution is almost free — just remember to filter out your own read calls, fail soft, and fetch it lazily.

This is the drift-risk view of a self-hosted tool that watches how your live AWS
drifts from Terraform — open source (MIT), one docker compose up:
syncvey.com. When drift shows up in your infra, what tells
you who did it today — CloudTrail by hand, a SIEM, or nobody and you just ask
around?

In information_schema, a generated column looks exactly like a plain one

TABATA Hitoshi — Mon, 29 Jun 2026 11:56:05 +0000

I was rendering a \d-style column list — name, type, nullable, default — straight off information_schema.columns. Clean, portable, boring. Then I pointed it at a table with a generated column and the view was quietly, confidently wrong.

The generated column showed up as a perfectly ordinary column. Type: fine. Nullable: fine. Default: NULL. No hint anywhere that the value is computed, not stored. If you trusted my panel, you'd think you could just write to it.

CREATE TABLE orders (
    qty   int,
    price numeric,
    total numeric GENERATED ALWAYS AS (qty * price) STORED
);

SELECT column_name, data_type, is_nullable, column_default
FROM information_schema.columns
WHERE table_name = 'orders';

 column_name | data_type | is_nullable | column_default
-------------+-----------+-------------+----------------
 qty         | integer   | YES         |
 price       | numeric   | YES         |
 total       | numeric   | YES         |          ← computed, but you can't tell

total is a column you literally cannot INSERT into, and information_schema describes it identically to qty. The standard view just doesn't carry the bit.

The bit lives in pg_attribute

PostgreSQL records "is this column generated" in pg_attribute.attgenerated:

'' — ordinary column
's' — STORED (the value is materialized on write)
'v' — VIRTUAL (computed on read; new in PostgreSQL 18)

So you stop trusting information_schema for this one fact and join down to the catalog:

SELECT c.column_name, c.data_type, c.is_nullable, c.column_default,
       a.attgenerated AS generated,
       CASE WHEN a.attgenerated <> ''
            THEN pg_get_expr(ad.adbin, ad.adrelid) END AS gen_expr
FROM information_schema.columns c
LEFT JOIN pg_attribute a
       ON a.attrelid = format('%I.%I', c.table_schema, c.table_name)::regclass
      AND a.attname  = c.column_name
      AND NOT a.attisdropped
LEFT JOIN pg_attrdef ad
       ON ad.adrelid = a.attrelid AND ad.adnum = a.attnum
WHERE c.table_schema = 'public' AND c.table_name = 'orders'
ORDER BY c.ordinal_position;

 column_name | generated |   gen_expr
-------------+-----------+---------------
 qty         |           |
 price       |           |
 total       | s         | (qty * price)

Now total carries its truth: generated, stored, computed from qty * price. Three small gotchas earned that result.

Join pg_attribute by name, and skip dropped columns. attnum is only stable if you exclude dropped columns (NOT a.attisdropped) — a dropped column leaves a tombstone row that shifts nothing if you account for it and corrupts your mapping if you don't. Matching on attname against information_schema's already-filtered list keeps the two sides aligned.

The expression comes from pg_attrdef, not pg_attribute. The generation expression is stored where defaults live, and you decompile it with pg_get_expr(ad.adbin, ad.adrelid).

Guard pg_get_expr with the CASE. This is the one that bites. A plain column with a normal default also has a pg_attrdef row — its adbin is the default literal, not a generation expression. Without WHEN a.attgenerated <> '' you'd happily print now() or 0 into a "generated from" column and mislabel every defaulted column in the table. The CASE keeps pg_get_expr pointed only at columns that are actually generated.

Why now: PostgreSQL 18 flips the default

Generated columns aren't new — STORED arrived in PostgreSQL 12, and attgenerated has existed since then, so the query above is version-robust all the way back. What changed is the blast radius.

PostgreSQL 18 adds VIRTUAL generated columns (computed on read, taking no storage) and makes VIRTUAL the default — write GENERATED ALWAYS AS (…) with no STORED/VIRTUAL keyword and you get a virtual one. Which means generated columns are about to get a lot more common in schemas written by people who never typed the word "generated" with intent. Every \d-clone built on information_schema is going to start being wrong about more tables, not fewer. The fix is the same one byte: read attgenerated.

The same hole in MySQL

MySQL has the identical trap from the other direction: information_schema.COLUMNS does carry the info, just smuggled into a free-text EXTRA field that also holds auto_increment and on update flags:

SELECT COLUMN_NAME, EXTRA, GENERATION_EXPRESSION
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = ? AND TABLE_NAME = ?;

EXTRA reads STORED GENERATED or VIRTUAL GENERATED (and GENERATION_EXPRESSION is '' for ordinary columns). You substring-match it rather than =, because the flags can combine. Same concept, same two kinds — stored vs virtual — different cubbyhole.

The honest part

This doesn't make a generated column writable or change anything about it — it just stops a schema viewer from lying by omission. If all you ever do is SELECT *, you never noticed the gap. The moment you build tooling that says "here are this table's columns and here's what you can put in them," the one byte in attgenerated is the difference between a true map and a confident wrong one.

This is one piece of cli2ui — a local-only web UI over the psql commands you keep half-remembering. No AI, no SaaS. It's MIT-licensed on GitHub. What does your \d clone quietly get wrong?

My dashboard showed every number and helped you decide nothing

TABATA Hitoshi — Fri, 26 Jun 2026 10:32:46 +0000

My dashboard looked great. Total assets, systems, environments. Counts broken
down by resource type, by category, by provider. Little badges everywhere. It
felt like a control room.

Then I noticed something about my own behavior: I never actually did anything
from it. I'd glance at the numbers, come away none the wiser, and then click into
a sub-page to find out whether I should care. The dashboard was the thing I passed
through on the way to the thing I needed.

That's the tell. A dashboard that you skim and leave isn't a dashboard — it's a
table of contents with nicer fonts.

The three layers, and where mine stopped

A useful dashboard answers three questions in order:

What's happening? — the signal. "Drift: 5."
Is that urgent? — the context. 5 up from 0 last scan is an incident; 5 down from 12 is progress. Same number, opposite meaning.
What do I do about it? — the action. A link straight into the workflow.

Mine was 100% layer 1. Here's literally what fed it:

return {
    'total_assets':   sum(by_type.values()),
    'total_systems':  sys_qs.count(),
    'total_envs':     env_qs.count(),
    'by_type':        by_type,
    'by_category':    by_category,
    'by_provider':    by_provider,
}

Raw counts. Not one of them tells you whether today is better or worse than
yesterday, and not one of them is a thing you can click and act on.

The embarrassing part: I had already built layer 2

This is the bit that stung. The context I was missing wasn't actually missing. I'd
already built it — it was just sitting in pages nobody lands on:

a DriftSnapshot history table that records drift after every scan (the trend)
end-of-life data for every tracked runtime and dependency
a ScanJob row with finished_at for every scan (the freshness)

All the materials for "is it urgent?" existed. I'd just never wired them into the
one screen where someone decides what to do next. The fix wasn't new data. It was
moving signal to where the decision happens.

The hero band

So I added a persistent row of three tiles above everything else — the first thing
you see, answering all three questions:

Tile	Signal	Context	Action
Drift	open drift across envs	▲/▼ vs the previous snapshot	→ drift history of the worst env
End of life	dependencies past EOL	`+N nearing EOL`	→ runtimes
Freshness	last completed scan	turns amber after 24h	—

The interesting work is the context column. Take the drift delta. I want it to say
"5 drifted (▲2 since last scan)". That means: for each environment, compare its
newest snapshot to the one before it. The naive version is a query per environment.
But you can do it in one, by ordering and grouping in Python:

rows = (
    DriftSnapshot.objects
    .filter(environment__system__organization=org)
    .order_by('environment_id', '-detected_at')
    .values('environment_id', 'changed_count', 'added_count')
)
latest_by_env, prev_by_env = {}, {}
for r in rows:
    env_id = r['environment_id']
    total  = r['changed_count'] + r['added_count']
    if env_id not in latest_by_env:        # first seen = newest (ordered desc)
        latest_by_env[env_id] = total
    elif env_id not in prev_by_env:        # second seen = the one before it
        prev_by_env[env_id] = total

One ordered scan, two dicts. (It's one query, but it does lean on a sort across all
snapshots — at serious scale that's the cost to watch, not the round-trips.) The
delta only compares environments that actually have a previous snapshot. A
brand-new environment shows its count with no misleading "▲5" — there was no prior
scan to compare against:

drift_current = sum(latest_by_env.values())
has_history   = bool(prev_by_env)
drift_delta   = None
if has_history:
    cur  = sum(latest_by_env[e] for e in prev_by_env)
    prev = sum(prev_by_env.values())
    drift_delta = cur - prev

Note that cur re-sums only the environments that have history, so the delta
compares like with like — it deliberately isn't the same number as drift_current.

The layout gotcha: the hero kept disappearing

My app is server-rendered Django with htmx. The sidebar doesn't navigate pages — it
swaps the contents of #main-content in place. My first attempt put the hero band
inside that container, and it worked beautifully until I clicked "All Resources"
and watched my brand-new dashboard evaporate. Of course it did: I'd just told htmx
to replace the element it lived in.

The fix is a one-line placement decision — the band has to live outside the
swap target:

</header>

{% include '_dashboard_hero.html' %}   {# persistent — survives htmx swaps #}

<main id="main-content" class="p-6">
    {% include '_system_list.html' %}
</main>

If you build a "shell + swappable body" UI, anything that's supposed to be
always-on — global signals, freshness, alerts — belongs above the swap boundary,
not in it. Obvious in hindsight; invisible until you click the wrong button.

Freshness and the empty state

Two details that decide whether people trust a dashboard.

Freshness, because a confident number from stale data is worse than no number.
Anything older than a day gets an amber tint:

last_scan_stale = (
    last_scan is None
    or (timezone.now() - last_scan).total_seconds() > 86400
)

The empty state, because a fresh install has zero of everything, and zero
should read as calm, not broken. When there's no drift, the tile isn't a stark
"0" — it's a green "No drift detected." A clean account should feel reassuring, not
like something failed to load.

What I deliberately left undone

Two honest edges, because the point is to ship the decision layer, not to gold-plate:

The signals are still computed live on every request. Fine at my scale; the right next step is a periodic job writing a summary row the dashboard just reads. Until aggregation actually hurts, precompute is speculative.
The new tiles are English-only for now — the translations are a follow-up.

Takeaways

If you skim your own dashboard and then leave to go find out if you should care, it's all signal and no decision. Start from "what is the user trying to decide here?", not "what data can I show?"
A raw count is layer 1. The same count versus yesterday is what makes it actionable. The delta is usually cheaper than you fear — one ordered query and a pass in Python.
In a shell/swappable-body UI, always-on signals must live outside the swap target, or they vanish on the first navigation.
Show data freshness, and make the empty state feel calm. Both are trust.

This is the dashboard of a self-hosted tool that tracks how your live AWS drifts
from Terraform, plus runtime EOL — open source (MIT), one docker compose up:
syncvey.com. When you design a dashboard, where do you draw
the line on context — just a number, a delta vs last run, or a full sparkline?

No SPA: a multi-panel database UI in Django + htmx + a sprinkle of Alpine

TABATA Hitoshi — Fri, 26 Jun 2026 10:31:13 +0000

I built a database console with a table browser, a SQL runner, an index lab, an EXPLAIN-plan differ, live activity and locks panels, snapshots, and a streaming backup/restore. A dozen panels, lots of moving parts.

There is no build step. No node_modules. No bundler, no framework CLI, no npm run anything. The <head> is three <script> tags from a CDN:

<script src="https://cdn.tailwindcss.com"></script>
<script src="https://unpkg.com/htmx.org@2.0.4"></script>
<script defer src="https://unpkg.com/alpinejs@3.14.8/dist/cdn.min.js"></script>

That's the whole frontend toolchain. And the surprising part isn't that it works — it's that it made the app deeper, because adding a panel got boring.

The one pattern

Every section of the UI is the same three pieces: one nav button, one view, one template. Click the button, htmx fetches a partial, swaps it into the main pane. That's it.

The nav button just declares where to fetch and where to put it:

<button hx-get="{% url 'overview' connection.pk %}"
        hx-target="#detail" hx-swap="innerHTML">⌂ overview</button>

<button hx-get="{% url 'query' connection.pk %}"
        hx-target="#detail" hx-swap="innerHTML">SQL</button>

<button hx-get="{% url 'history' connection.pk %}"
        hx-target="#detail" hx-swap="innerHTML">History</button>

The view does the database work and renders a plain Django template fragment — no JSON, no serializer, no client-side rendering:

def table_detail(request, pk):
    """Columns + a row preview for one table (htmx partial into #detail)."""
    connection = get_object_or_404(Connection, pk=pk)
    engine = get_engine(connection)
    return render(request, "partials/detail.html", {
        "columns": engine.list_columns(schema, table),
        "indexes": engine.list_indexes(schema, table),
        "preview_rows": engine.preview_rows(schema, table).rows,
    })

The template is the same HTML you'd write for a full-page render, minus the <html> wrapper. The server already knows how to turn data into markup — that's what templates are. htmx just lets you ship a fragment of that markup to a fragment of the page.

So "add a panel" is purely mechanical: add a path(), write a view, write a template, drop in a button. There's no new state to wire into a client store, no API contract to keep in sync, no round-trip of "shape the JSON / reshape it back into DOM." That low ceremony is why the panel count grew — coverage got wide because each addition was cheap.

Forms are just buttons that POST

Mutations are the same shape, with hx-post and a confirm where it bites:

<form hx-post="{% url 'table_truncate' connection.pk %}"
      hx-target="#detail" hx-swap="innerHTML"
      hx-confirm="Truncate this table? This permanently deletes every row.">
  <button class="btn btn-danger">Truncate</button>
</form>

The view re-renders the same detail partial afterward, so the panel updates itself with the now-empty preview. No optimistic UI, no manual DOM patching — the server re-states the truth and htmx swaps it in.

When one action needs to touch two places — say a rename updates the main pane and the sidebar tree — you don't reach for client state. You append an out-of-band fragment to the same response:

# Same round trip: re-render the sidebar and let htmx place it by id.
response.content += render_to_string(
    "partials/table_list.html",
    {"tables": tables, "oob": True}, request=request,
).encode()

<div id="table-list"{% if oob %} hx-swap-oob="true"{% endif %}>…</div>

Two regions, one request, zero JavaScript.

Where Alpine earns its keep

If the server owns navigation and data, what's left for JS? Only the genuinely client-side bits — state the server has no opinion about:

a slide-over drawer that toggles open/closed,
a filter builder where you add/remove condition rows,
copy-to-clipboard, and a spinner while a file download streams.

That's exactly Alpine's lane: a few lines of local component state, no global store for things the database already knows. The filter builder is fifteen lines of x-data, not a Redux slice.

And there's almost no boilerplate tax for the no-build choice. CSRF is wired once, on <body>, so no form needs a hidden token:

<body hx-headers='{"X-CSRFToken": "{{ csrf_token }}"}'>

The honest part: where this breaks

This is not "SPAs are bad." htmx-over-the-wire is the right tool when the server stays the source of truth and interactions are request/response shaped — click, fetch, swap. A database console is exactly that: every panel is a question you ask the database and a fresh answer it renders.

It would be the wrong tool the moment you need rich, offline-ish client state: a drag-and-drop canvas, a collaborative editor, an interactive chart you pan and zoom, anything where the UI holds significant truth the server isn't round-tripping. Push htmx there and you'll fight it — re-fetching for interactions that should never hit the network. Each section swap is also a real HTTP request, so a truly chatty, keystroke-latency UI wants a client framework.

But for a tool that's fundamentally "show me what the database says, then let me change it," giving up the build step cost nothing and bought a codebase where the next feature is four small, obvious files.

This is one piece of cli2ui — a local-only web UI over the psql commands you keep half-remembering. No AI, no SaaS. It's MIT-licensed on GitHub. What command do you reach for that should be a button?