DEV Community: TABATA Hitoshi

Eight of my eighteen AWS scanners failed against a fake AWS — and that was the point

TABATA Hitoshi — Thu, 27 Aug 2026 13:10:09 +0000

You cannot try a self-hosted AWS tool without an AWS account. That is a real wall: the thing needs credentials, a role to assume, and something in the account worth scanning before it shows you anything at all.

So I pointed it at a fake AWS — LocalStack, in a Compose profile, with a seed script that fills it with resources. Then I ran the scanner.

Ten of the eighteen scanners worked. Eight failed. The free edition of the
emulator does not implement those services, so they returned errors.

That was the interesting part.

A scan that stops at the first error cannot tell you what was deleted

Drift detection is not only "this attribute changed". The category that actually bites is the resource that is no longer there — someone deleted it in the console, and terraform plan says nothing because it was never in the state.

To report a deletion, you have to finish the sweep. A resource is gone only if you looked everywhere it could have been and did not find it. Which means: a scanner that aborts on the first failing service is structurally incapable of detecting deletions. It cannot tell "not there" from "I stopped early".

I had written it to carry on and report an error per service, because that is what deletion detection needs. Running against an emulator that fails eight of eighteen services was the first time that design was tested for real — the sweep completed, the eight reported their own failures, and the deletion detection on the other ten still worked.

The emulator did not just make the tool easier to try. It exercised the failure mode the tool depends on being right about.

Try it

git clone https://github.com/MR-TABATA/SyncVey
cd SyncVey
docker compose --profile demo up -d
python scripts/seed_localstack.py

No account, no credentials, no bill. The README says which scanners work on the emulator's free edition and which do not, CloudTrail attribution included.

Starting it is also faster than it used to be: docker compose up -d now pulls a published image instead of building one on your laptop (0.4.0). Building from source is still one command away — cp docker-compose.override.yml.example docker-compose.override.yml — and gives you live reload.

Repo: https://github.com/MR-TABATA/SyncVey
Tour: https://syncvey.com/
How it got here, release by release: https://syncvey.com/history.en.html

pg_total_relation_size() takes a lock. My locks page waited behind the lock it was there to explain.

TABATA Hitoshi — Thu, 27 Aug 2026 11:32:56 +0000

An ALTER TABLE is waiting on an idle transaction. Ten sessions are queued behind it.
You open the tool that exists to tell you which session to kill — and the page never
returns.

That was cli2ui, three weeks ago, on my own machine. The Locks panel itself was fine:
it answered in 0.02 seconds. What never came back was the page on the way to it.

The overview and the Health panel both show table sizes, and table sizes come from
pg_total_relation_size() and pg_table_size(). Those functions open the relation
they measure. Opening a relation takes ACCESS SHARE. And ACCESS SHARE queues behind
the ACCESS EXCLUSIVE that the stuck ALTER TABLE is holding.

So the probe that measures your tables waits for exactly the thing you opened the tool
to investigate. Nothing in that query looks dangerous — it is read-only, it touches no
user data, it is the sort of thing you put on a dashboard without thinking. It still
takes a lock.

Read-only and lock-free are different properties. I knew that sentence before this
happened. I had still put a lock-taking probe on the path to the lock-diagnosis page.

The fix is small and worth stating plainly, because the instinct is to remove the size
cards and I do not think that is right — they are useful the other 99% of the time:

both probes now run under a 1-second lock_timeout
when they time out they report what happened instead of failing the request
each degrades its own card, so the Health panel keeps every card a lock cannot touch

The tool now gets less useful during a lock jam, rather than unreachable. That is the
trade I want: a diagnostic tool has to be at its most reliable in exactly the situation
it exists for.

MySQL is unaffected, incidentally, and for a reason worth knowing: its sizes come from
information_schema.TABLES, which reads the data dictionary rather than opening the
relation.

The other half: starting the app is now a pull, not a build

Same release, unrelated lesson. docker compose up used to run apt and pip on your
laptop before showing you the first screen — a laptop that has no reason to compile
anything. Compose now points at a published image (jiniie/cli2ui:latest, amd64 and
arm64, pushed on v* tags), so starting the app is a pull. CLI2UI_IMAGE in .env
pins a version. Building from source moved to docker-compose.override.yml.example —
copy it and you get build: . plus the working-tree mount, i.e. the old behaviour with
live reload.

If you already have a checkout, read this before upgrading. The default compose
path no longer mounts .:/app, so the management database now lives on a named volume
(CLI2UI_DB_PATH → /data). A checkout with saved connections will start empty:
nothing was deleted, the app is looking somewhere else. Copy your db.sqlite3 into the
volume, or copy docker-compose.override.yml.example to put the file back in the
project root where you can see it.

That is also why :latest already moved: if you pull today, you get 1.4.0.

pg_cancel_backend() returned true. The queue didn't move.

TABATA Hitoshi — Tue, 25 Aug 2026 10:27:02 +0000

Ten sessions are stuck. You skip the slow-query hunt, do the hard part properly, and find the one session at the front of the queue — the one holding a lock that everyone else is waiting on.

You press cancel.

Postgres says t. Nothing happens.

Not "nothing happens yet." Nothing happens at all. The session is still there, the locks are still held, and the nine sessions behind it are exactly where they were. The call succeeded and did nothing, which is a worse outcome than failing, because a failure would have told you to try something else.

Cancel cancels a query. That session isn't running one.

pg_cancel_backend(pid) sends SIGINT to a backend and interrupts the query it is currently executing. That's the whole contract. The return value means the signal reached a valid backend — not that any work stopped.

Now look at what the classic jam actually is. A client ran a statement inside a transaction, took its locks, and then went quiet without committing. Postgres calls that state idle in transaction. There is no query running. There is nothing for SIGINT to interrupt.

And the locks? They belong to the transaction, not to the statement that acquired them. Cancelling a query — even a real one — doesn't end the transaction. On a session that isn't running a query, cancel has nothing to grab at either end.

The measurement

Two sessions, one row. Session A updates it inside a transaction and then does nothing. Session B tries to update the same row.

 pid |        state        | wait_event_type | blocked_by |              query
-----+---------------------+-----------------+------------+----------------------------------
  99 | idle in transaction | Client          | {}         | UPDATE t SET v='held' WHERE id=1
 120 | active              | Lock            | {99}       | UPDATE t SET v='B' WHERE id=1;

pid 99 is the head. Fire cancel at it:

SELECT pg_cancel_backend(99);

 cancel_returned
-----------------
 t

Three seconds later:

 pid |        state        | wait_event_type | blocked_by
-----+---------------------+-----------------+------------
  99 | idle in transaction | Client          | {}
 120 | active              | Lock            | {99}

Identical. Same state, same wait, same blocker. Now terminate instead:

SELECT pg_terminate_backend(99);

 terminate_returned
--------------------
 t

 id | v
----+---
  1 | B

Session B's update lands immediately. Same pid, same t, completely different outcome.

The activity list is showing you a fossil

Go back to that first table and look at the query column for pid 99:

UPDATE t SET v='held' WHERE id=1

That session is not running that query. It finished it a while ago. pg_stat_activity.query is the last statement the backend ran, not a live one — and for an idle session it just sits there, looking exactly like work in progress.

This is why the usual instinct fails. You open the activity list, you see a session with a plausible-looking UPDATE, and every visual cue says "here is a query, cancel it." The one column that would have told you the truth is state, which is the column nobody sorts by.

wait_event_type gives it away too, once you know to look: pid 99 says Client. It isn't waiting on a lock or on I/O. It's waiting on you — on the application, to send the next statement. It'll wait all afternoon.

So the button was sitting exactly where it can't work

I found this while testing something else, and the annoying part wasn't the Postgres behaviour. It was the realisation that in my own tool, the cancel button was rendered on every session in the panel — including at the top of a wait-for chain, which is precisely where the head blocker is idle in transaction.

The button was placed with maximum prominence at the one spot where it is guaranteed to be a no-op. It would even report success.

The rule to fix that looks like a one-liner:

@property
def cancellable(self) -> bool:
    """Whether "cancel" has anything to act on. It stops the *running query*
    (pg_cancel_backend / KILL QUERY), so a session that isn't running one —
    idle, or idle in transaction — accepts the signal and changes nothing;
    pg_cancel_backend even returns true."""
    return self.state == "active"

One line, and one line too coarse

Apply that same rule inside a wait-for tree and you break the sessions that were fine.

A blocked session — one of the nine stacked behind the head — is waiting on a lock. A lock wait only ever happens part-way through a statement: the backend started executing, reached the point where it needed the lock, and stopped there. There is a real, live query to cancel. Cancel works on it.

But in the tree, that node's state is empty. The row it came from is a lock-wait row, which describes what this session is waiting for — the mode, the object, the duration. Only the blocker side of the join carries session state. So state == "active" is false for every victim, and the naive rule disables cancel on precisely the sessions where it works.

The fix is to read the fact that's actually present:

@property
def cancellable(self) -> bool:
    """Same rule as Activity.cancellable, and it bites hardest here: the
    classic head blocker is idle in transaction, where cancel is a no-op.

    A node that carries wait fields is blocked *on a lock*, which only
    happens part-way through a statement — so it has a running query to
    cancel even though `state` is unset for it."""
    if self.lock_mode is not None:
        return True
    return self.state == "active"

If it's waiting for a lock, it's mid-statement. That's not an inference about state — it's what a lock wait is.

Don't hide it, and don't wait until afterwards

Two tempting designs, both worse:

Hide the button. Now the head blocker is the only row with no controls, and the operator wonders whether the tool is broken.

Let them press it and show an error. There is no error. Postgres returns t. You'd have to invent a failure that didn't happen.

What's left is to render it disabled, in place, carrying its own reason:

This session is not running a query, so there is nothing to cancel — it would report success and release nothing. Only kill ends its transaction.

The control stays where the eye expects it, and it explains itself at the moment of the mistake instead of after it.

MySQL has the same split

KILL QUERY <id> stops the running statement; KILL <id> (or KILL CONNECTION) drops the connection and rolls the transaction back. Same distinction, same trap — a connection sitting in Sleep with an open transaction has no statement for KILL QUERY to take.

The state names differ, so normalise once: a session running a statement is reported as active on both sides (MySQL's COMMAND='Query' maps onto it). After that, one rule reads correctly on either engine.

The honest part

Terminate isn't free, and I'm not selling it as the answer. You are throwing away a transaction that someone — some service, some human with a psql window — believes is still open. The application gets a dropped connection, and how gracefully it handles that is not your call to make from a monitoring panel.

Cancel is still the right tool for the case it was built for: a genuinely long-running active query you want to stop without killing the session. Nothing here says otherwise.

And the real fix is upstream of both buttons. A session that goes idle in transaction for four minutes is an application bug — a transaction opened around a network call, a with block that isn't there, a connection returned to the pool without a rollback. idle_in_transaction_session_timeout will end them for you on a schedule, which beats noticing by hand at 2am.

But when you are noticing by hand at 2am, it matters a great deal that the button you press does something. t is not the same as done.

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 longest idle in transaction you've caught in production?

I deleted a queue and my own CLI told me a resource had been added

TABATA Hitoshi — Sat, 22 Aug 2026 23:00:39 +0000

Last week I finally did something I'd been putting off for months: I pointed my AWS drift detector at a fake AWS.

Not a mock in a unit test — a real emulator listening on a real port, speaking the real API. I created a handful of resources, scanned them, then deleted an SQS queue and scanned again to watch the tool notice. Here is what it printed:

Demo System / Production: 3 drifted (changed=1 added=2)
    ~ EC2 i-9538ffc1...   instance_state: running -> stopped
    + S3  demo-assets-2
    + SQS demo-jobs          <-- the queue I had just deleted

The web UI, looking at the same database, said the queue was removed. The CLI said it was added. Same data, opposite verdicts, and only one of them was going to be in front of someone at 2am.

Why a deletion looked like a creation

My scanner does not delete rows for resources that vanish from AWS. It stamps missing_since and leaves the row alone, so you can still answer "what was there, and when did it go?" months later.

Drift classification then looked at two fields. raw_data is the latest scan, raw_data_prev is the one before it. The CLI's rule read:

if not asset.raw_data_prev:      # never had a previous value
    added.append(asset)          # ...so this is the first time we've seen it

That reads fine right up until you notice raw_data_prev is empty in two opposite situations:

a resource that just appeared and has only been scanned once
a resource that appeared and disappeared before a second scan could confirm it

The field is a proxy for "we have no history here." It cannot tell you why there's no history. The only field that knows a resource is gone is missing_since — and the CLI never looked at it.

The correct order isn't clever, it's just an order. Existence first, attributes second:

if asset.missing_since:          # did it disappear?
elif not asset.raw_data_prev:    # did it appear?
else:                            # did its attributes change?

The core already did exactly this. So did the drift report. So did the history snapshot writer. The CLI did not.

The part that actually stings

I wrote the fix in about four minutes, and then went looking for how it happened — and found four copies of the same decision tree:

Where	What it produces
environment badge	counts for a card
snapshot writer	rows for drift history
drift report view	sections for a page
CLI	text, JSON, and a CI exit code

Four callers, four hand-written copies of "what category is this asset in."

This is not the first time it bit me. When I added the removed category, some copies learned it and some didn't. A later fix updated two of them. The CLI was the fourth, and it stayed wrong for two releases because nothing forces copies to agree.

The genuinely embarrassing part: my previous post ended with advice about this. I'd put the Auto Scaling ownership check in one shared helper so all four paths would inherit it, and wrote a smug little paragraph about how a tool that disagrees with itself is worse than no tool. All true — and the classification around that helper was still copy-pasted four times. I'd deduplicated the thing I had just been burned by, and left the frame it sat in alone.

The fix: callers stop deciding

One function now owns the decision, and returns nothing but the decision:

# asset_manager/drift.py
def classify(asset) -> tuple[str, list]:
    """Existence first, attributes second."""
    if asset.missing_since:
        return (AUTOSCALING if is_autoscaling_churn(asset.raw_data) else REMOVED), []
    if not asset.raw_data_prev:
        return (AUTOSCALING if is_autoscaling_churn(asset.raw_data) else ADDED), []
    changes = _compute_raw_diff(asset.raw_data_prev, asset.raw_data)
    return (CHANGED, changes) if changes else (UNCHANGED, [])

Each caller keeps only what genuinely differs — counts for the badge, JSON for the CLI, template rows for the page. None of them mentions missing_since any more, which means none of them can get this wrong again.

Worth noting what did not get shared: the output shapes. A badge needs integers, a report needs ORM objects, a CLI needs strings. Trying to unify those too is how you end up with a "flexible" function taking four keyword arguments that each caller sets differently — which is four copies again, wearing a trench coat. Share the decision, not the presentation.

The test I should have written two releases ago

Per-rule tests are the easy half (a deletion is REMOVED, a scale-in is churn, an ASG instance's attribute change is still real drift). The one that earns its keep is the one that doesn't test a rule at all:

def test_badge_snapshot_and_cli_report_the_same_counts(self):
    badge    = _get_env_drift_summary(self.env)
    snapshot = _record_drift_snapshot(self.env, DriftSnapshot.Source.SCAN)
    cli      = drift_for(self.env)

    self.assertEqual(badge['removed'], snapshot.removed_count)
    self.assertEqual(len(cli['removed']), snapshot.removed_count)

It asserts no specific behaviour. It asserts that my four answers are the same answer. If someone (me, in four months, in a hurry) writes a fifth copy that drifts, that's a red build instead of a support email.

Why the emulator found this and my test suite didn't

My unit tests mock AWS at the library layer, and they're good tests — 200+ of them, all green while this bug shipped. They were green because I wrote them from the same mental model that produced the bug. I never wrote "create a queue, delete it, scan twice, assert it reads as removed," because it didn't occur to me that it wouldn't.

The emulator is what made that sequence cheap enough to do idly. No AWS account, no credentials, no bill — start a container, make things, break things, watch the tool. The bug surfaced in about ten minutes of poking, not from rigor but from playing with the actual product.

Honest notes from doing it:

The free tier of the emulator implements a subset of AWS. 10 of my 18 scanners returned data; the rest reported an error per service. That was useful in itself: it exercised the path where a scanner fails, and confirmed a failed scanner is excluded from deleted-resource detection — an unreadable service must never read as "the customer deleted their fleet."
CloudTrail attribution ("who changed this?") doesn't work there at all. Some things still need a real account.
:latest is now a licensed build that exits immediately without an auth token. Pin the major version tag or your first impression is a dead container.

Takeaways

A field that's empty for two opposite reasons is a bug waiting for a quiet afternoon. raw_data_prev meant both "brand new" and "already gone." If you're branching on absence, ask what else could make it absent.
Judge existence before attributes. Did it appear, did it disappear, and only then, did it change. Get the order wrong and deletions come out as creations.
Deduplicating one helper doesn't deduplicate the decision around it. I shared the ASG check and left four copies of the classification that used it.
Test that your surfaces agree, not just that each one works. Every copy passed its own tests while disagreeing with the others.
Run your own tool for fun, not just in CI. An emulator makes that free, and playing found in ten minutes what 200 green tests missed for two releases.

This is a real, open-source (MIT) self-hosted tool that tracks how your live AWS drifts from Terraform — one docker compose up, and now with a --profile demo that runs it against a local emulator so you can try it with no AWS account at all: syncvey.com.

Have you ever found two parts of your own system confidently disagreeing about the same data — and which one did you trust first?

docker pull jiniie/syncvey

My drift detector cried wolf every time my autoscaler scaled out — and the fix was a tag AWS already sets

TABATA Hitoshi — Thu, 30 Jul 2026 12:14:52 +0000

A while back I wrote about the first time my self-hosted drift detector cried
wolf: a diff bug that flagged phantom changes on every scan. I fixed it and
felt clever.

Then I pointed it at an account with an Auto Scaling group, and it cried
wolf again — for a completely different reason, and this time the tool was
technically right.

Every time the ASG scaled out, the new instance had an instance ID the previous
scan had never seen. My drift report dutifully counted it as an added
resource. Scale in, scale out, Tuesday afternoon traffic — a steady drip of
"drift" that was nothing of the sort. The autoscaler launching an instance isn't
your infrastructure drifting from Terraform. It's the system working exactly as
designed.

Why "added" is the wrong verdict here

Attribute drift is easy to reason about: tfstate says t3.micro, live AWS says
t3.large, that's drift. "Added" is trickier, because a resource that wasn't
there and now is usually means someone created something out-of-band — the
exact console click I built this tool to catch.

But an ASG is different. Terraform describes the group — min, max, desired,
launch template. It does not describe each individual instance; those are the
autoscaler's runtime decisions. So the identity and count of instances inside an
ASG are never "drift from Terraform." They're supposed to change on their own.

Counting them as drift doesn't just inflate a number. It trains everyone to
ignore the drift report, which quietly kills the one signal you actually needed.

The fix I almost over-engineered

My first instinct was to call DescribeAutoScalingGroups, list each group's
members, and cross-reference. That means a new scanner and a new IAM permission
(autoscaling:DescribeAutoScalingGroups) on every deployment.

Then I looked at what I was already scanning. EC2 stamps every ASG-launched
instance with a reserved tag — aws:autoscaling:groupName — and my EC2 scanner
already reads instance tags. The ownership signal was sitting in my data the
whole time. No new API call, no new permission:

# asset_manager/autoscaling.py — one source of truth for "does an ASG own this?"
ASG_TAG = 'aws:autoscaling:groupName'

def autoscaling_group_of(raw_data):
    if not raw_data:
        return None
    # explicit key from the current scanner, else the tag we already had —
    # so instances scanned before this feature existed classify correctly too
    return raw_data.get('autoscaling_group') or (raw_data.get('tags') or {}).get(ASG_TAG) or None

Users can't set the aws: tag namespace themselves, so its presence is a
reliable "the autoscaler owns this" flag. This is the third time on this project
that the honest fix was "the data already told me" rather than a new API call —
I'm starting to think that's the default, not the exception.

The one line that keeps this from being a foot-gun

Here's the part I want to get right, because the naive version is dangerous.
It's tempting to say "ignore drift on ASG-owned instances." Don't. If you
suppress all drift on autoscaled instances, you go blind to a security group
opened to 0.0.0.0/0 on your entire web fleet — which is exactly the drift you
most want to catch.

So I suppress only the existence dimension — a first-sighting instance the
autoscaler created — and nothing else:

if not asset.raw_data_prev:
    # first sighting: churn if an ASG owns it, otherwise a real "added"
    if is_autoscaling_churn(asset.raw_data):
        autoscaling.append(meta)
    else:
        added.append(meta)
else:
    # an ASG instance that CHANGED attributes is still real drift — reported
    diff = _compute_raw_diff(asset.raw_data_prev, asset.raw_data)
    if diff:
        changed.append({**meta, 'changes': diff})

A new autoscaled instance → churn, filed away. That same instance later showing
an opened security group → drift, front and center. And I show the suppressed
instances in their own "Auto Scaling — not drift" section rather than hiding
them, so the report is honest about what it chose not to alarm on.

One source of truth, or the CLI lies again

My tool computes drift in a few places: the dashboard badge, the recorded
history snapshot, the drift report page — and, since last week, a syncvey
command line. If each of those decided "is this churn?" on its own, they'd
eventually disagree, and a tool that disagrees with itself is worse than no
tool.

So the ownership check lives in exactly one helper that all four import. Which
surfaced a nice catch: the CLI I shipped in my last post has a
syncvey drift --exit-code flag meant to fail a CI build on drift. Before this
change, a routine scale-out would have turned a pipeline red for no reason.
The same one-line fix closed that hole automatically, because the CLI reads the
same function as everything else.

What I didn't fix

Honesty section, as always:

This suppresses scale-out churn (new instances). Scale-in leaves a stale asset row for a terminated instance — that's a separate "prune what's gone" problem I haven't solved here.
It trusts the tag. That's a safe bet for aws:autoscaling:groupName, but it's a tag check, not a live ASG-membership lookup — I traded a little precision for zero new IAM, on purpose.
It's on by default (DRIFT_SUPPRESS_AUTOSCALING), because churn-as-drift is almost never what you want — but you can turn it off if you really want to see every instance the autoscaler ever launched.

Takeaways

Not every "added" resource is drift. If a system is designed to create and destroy resources on its own (autoscalers, spot fleets, Karpenter), counting that as drift just teaches people to mute the alarm.
Before adding an API call and an IAM permission, check what you already collect. AWS reserved tags (aws:autoscaling:groupName, aws:cloudformation:*, aws:eks:*) carry a lot of ownership signal for free.
Suppress narrowly. "Ignore this resource" is a foot-gun; "ignore this one dimension of this resource" (its existence, not its config) keeps you from going blind to the drift that matters.
If several code paths answer the same question, give them one function to call. The bonus is every consumer — including a CLI you wrote later — inherits the fix.

This is a real, open-source (MIT) self-hosted tool that tracks how your live AWS
drifts from Terraform — one docker compose up:
syncvey.com. If you run autoscalers, how does your drift
tooling tell "the autoscaler did its job" apart from "someone changed something"?

My infra tool could show drift, grade it, and Slack it — but it still couldn't fail a CI build

TABATA Hitoshi — Wed, 22 Jul 2026 12:39:58 +0000

A while back I wrote about teaching my self-hosted drift detector to stop being
pull — instead of waiting for someone to open a tab, it now pushes a Monday
briefing into Slack. That fixed the human case.

It didn't fix the case where there is no human. A pull request opens, CI runs,
something merges — and the one moment where drift should be able to stop a
change cold is a pipeline that had no way to ask my tool anything. A dashboard
assumes eyes. A Slack digest assumes someone reading Slack. CI reads neither. It
reads exit codes.

So this time the feature is small and unglamorous: a command line.

python manage.py syncvey scan --system e-commerce   # live scan + record a snapshot
python manage.py syncvey drift --env prod           # what drifted, right now
python manage.py syncvey drift --exit-code          # exit 1 if anything drifted
python manage.py syncvey drift --format json        # same, for a pipeline to parse
python manage.py syncvey status                     # systems / envs / counts / last scan

The whole point is that fourth-from-top line. --exit-code turns drift into a
build gate:

- name: Fail on infrastructure drift
  run: python manage.py syncvey drift --exit-code

Now a console change that never went through Terraform can turn a pipeline red.

"But `terraform plan` already runs in CI"

This is the first objection, and it's fair — plenty of teams run terraform plan
in CI and call the non-empty diff a drift gate. The reason I still needed my own
command is the same reason this whole project exists: plan compares your
state to your config. It never compares either one to reality.

If someone opens a security group in the console, there's no Terraform resource
for it, so plan has nothing to diff. Exit 0. Pipeline green. The most dangerous
kind of drift — the click nobody wrote down — is exactly the kind plan is
structurally blind to.

syncvey drift diffs the live AWS state (scanned with boto3) against the last
snapshot. So --exit-code is the gate plan's exit code can't be. Same form
factor, different question.

Lesson 1: the plugin seam I already had didn't fit — and Django had a better one

I keep the advanced pieces of this tool as detachable apps: risk scoring,
CloudTrail attribution, blast-radius — each lives in its own Django app, and the
core is not allowed to import it. Install the app and the feature appears; remove
it and the core neither knows nor cares.

For web features, that seam is hand-built: a plugin advertises a nav entry and a
URL, and a feature flag guards the route.

class BlastRadiusConfig(AppConfig):
    syncvey_plugin = True
    def nav_items(self, request):
        return [{'label': 'Blast Radius', 'url': reverse('blast_radius:home'), ...}]

My first instinct was to build the CLI the same way — invent some registry the
core reads to decide which subcommands exist. I got about two minutes in before
noticing I was reinventing something Django already does. Every app in
INSTALLED_APPS automatically contributes its management/commands/*.py. That
is the plugin seam. I didn't have to wire anything:

syncvey_cli/
  apps.py                         # syncvey_plugin = True, feature 'cli'
  service.py                      # the actual scan/drift logic
  management/commands/syncvey.py  # the command — discovered for free

Add syncvey_cli to INSTALLED_APPS → manage.py grows a syncvey command.
Remove it → the command is gone, and nothing in the core breaks. The
"detachable" property I'd carefully engineered for web features came for free
for a CLI, because command discovery is already how Django works. The lesson I
keep relearning: before building a seam, check whether the framework already
handed you one.

The AppConfig is almost empty — it just opts into the feature registry so the
rest of the app can answer "is the CLI installed?":

class SyncveyCliConfig(AppConfig):
    name = 'syncvey_cli'
    syncvey_plugin = True
    feature_name = 'cli'

No nav_items, no URL, no route guard. A CLI isn't a web surface, so it
contributes none of that — and the seam doesn't ask it to.

Lesson 2: a CLI that computes drift differently from your UI is a liar

Here was the tempting shortcut. The command needs to know what drifted. The core
already computes that for the dashboard, but its function is _compute_raw_diff
— underscore-prefixed, "private." Re-implementing a quick diff in the CLI would
have been fewer imports and felt cleaner.

It would also have been a bug — the worst kind, where two features quietly
disagree about the same fact.

The core diff has a hard-won rule in it. A live scan emits a curated ~11 fields
per resource; a tfstate import carries 50+. If you diff by the union of keys,
every tfstate-only field reads as "deleted" and the whole thing is a wall of
false drift. So the core compares the intersection of keys and detects real
attribute changes on shared ground:

keys = (set(old.keys()) & set(new.keys())) - _DIFF_EXCLUDE

If the CLI rolled its own diff, syncvey drift and the dashboard would report
different drift for the same environment. Which one do you trust? Neither, once
you've seen them disagree. So the CLI imports the exact same function the UI
uses:

from asset_manager.views import _compute_raw_diff

Yes, I imported a "private" helper across an app boundary. The rule I actually
care about — a plugin may depend on the core; the core never depends on a
plugin — is intact. And there is now exactly one definition of "what counts
as drift" in the codebase. The CLI can't drift from the dashboard because they
run the same code. syncvey scan reuses run_scan and the snapshot recorder for
the same reason: the command mirrors the web scan flow instead of forking it.

Exit codes are the actual product

For a human, the pretty text output is the feature. For CI, the exit code is
the feature and the text is incidental. So the codes are a deliberate contract:

0 — ok / no drift
1 — drift found (only with drift --exit-code)
2 — a scan job failed, or your --system / --env selector matched nothing

That last one matters more than it looks. A drift gate that exits 0 because it
silently matched zero environments is worse than no gate — it's a green light
that checked nothing. 2 keeps a typo'd --system from passing as "clean."

Takeaways

A monitoring tool that only speaks through a UI and a chat message still can't participate in the one place changes are gated: the pipeline. CI doesn't read dashboards; it reads exit codes. Give it one.
terraform plan in CI is a state-vs-config gate, not a drift gate. It can't fail on a resource that was never in Terraform. If you care about console-made drift, you need a check that reads live cloud state.
Before you build a plugin seam, check whether the framework already is one. Django discovers management commands from INSTALLED_APPS — a CLI plugin is detachable for free, no registry required.
If you add a second interface (CLI, API, export) over an existing feature, reuse the exact function, don't re-implement it. Two code paths that compute "the same" answer will eventually disagree, and then both are untrustworthy.
Make "matched nothing" a non-zero exit. A gate that passes because it checked zero things is the most dangerous kind of green.

This CLI ships with a self-hosted tool that tracks how your live AWS drifts from
Terraform — open source (MIT), one docker compose up:
syncvey.com. If you gate infra in CI today, what are you
gating on — terraform plan, a cloud-state scan, or nothing yet?

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?

DEV Community: TABATA Hitoshi

Eight of my eighteen AWS scanners failed against a fake AWS — and that was the point

A scan that stops at the first error cannot tell you what was deleted

Try it

pg_total_relation_size() takes a lock. My locks page waited behind the lock it was there to explain.

The other half: starting the app is now a pull, not a build

Links

pg_cancel_backend() returned true. The queue didn't move.

Cancel cancels a query. That session isn't running one.

The measurement

The activity list is showing you a fossil

So the button was sitting exactly where it can't work

One line, and one line too coarse

Don't hide it, and don't wait until afterwards

MySQL has the same split

The honest part

I deleted a queue and my own CLI told me a resource had been added

Why a deletion looked like a creation

The part that actually stings

The fix: callers stop deciding

The test I should have written two releases ago

Why the emulator found this and my test suite didn't

Takeaways

My drift detector cried wolf every time my autoscaler scaled out — and the fix was a tag AWS already sets

Why "added" is the wrong verdict here

The fix I almost over-engineered

The one line that keeps this from being a foot-gun

One source of truth, or the CLI lies again

What I didn't fix

Takeaways

My infra tool could show drift, grade it, and Slack it — but it still couldn't fail a CI build

"But terraform plan already runs in CI"

Lesson 1: the plugin seam I already had didn't fit — and Django had a better one

Lesson 2: a CLI that computes drift differently from your UI is a liar

Exit codes are the actual product

Takeaways

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

The ten that vanished when I picked the framework

The two that survived the filter

The actual lesson isn't the count

The honest caveat

Takeaways

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

One query, three fingerprints

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

Naming the connection that's stuck

MySQL has the count, not the breakdown

The honest part

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

What makemessages actually did

Why this is worse than an empty translation

The fix is a discipline, not a command

Takeaways

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

The whole ordering is one stdlib call

The cycle detection is the part you didn't write

The self-reference trap

Getting the edges: one catalog query

The same shape in MySQL

The honest part

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

The shape that breaks a diff

Two different questions, two different modules

What the grader actually looks at

The scan captures posture, never the secret

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

Honest limits

Takeaways

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

What the Terraform MCP server actually sees

The blind spot is the data source, not the interface

Start from the cloud, not from the state

To be fair to the MCP server

Takeaways

"But `terraform plan` already runs in CI"