DEV Community: ひとし田畑

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

ひとし田畑 — 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

ひとし田畑 — 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

ひとし田畑 — 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

ひとし田畑 — 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?

I shipped a database console with no login — on purpose

ひとし田畑 — Thu, 25 Jun 2026 10:20:17 +0000

"You built a web UI that runs SQL against a database, and there's no login?"

Yes. On purpose. And the reaction is worth unpacking, because "add authentication" is a reflex, and reflexes skip the question that actually matters: what's the threat you're defending against? For a tool that runs on your own machine, next to your own database, a login turns out to be the wrong tool for the real danger — and it drags in a liability you don't want.

What auth would actually cost

The moment you add accounts, you're holding credentials on a server. Now you own: password hashing, session management, reset flows, lockouts, and — because this thing also stores database connection passwords — an encryption-at-rest story for those too. You've signed up for a multi-tenant security posture to protect a tool that one person runs on localhost.

And here's the thing: none of that defends against the attack that can actually reach a local tool. If anything, being "logged in" makes it worse.

The real threat: the tab you forgot about

A web server on localhost:8000 isn't invisible just because it's local. Every other website open in your browser can send requests to it. So the realistic attack on a no-auth local console isn't "a stranger logs in as you" — it's a drive-by: some page you have open in another tab quietly fires a cross-origin POST at http://localhost:8000/... and your database executes a DROP.

Notice what a login does against that attack: nothing. You'd be logged in. The malicious form submits as you, with your cookies, to your authenticated session. Auth defends against the wrong actor — a remote impostor — when the actual actor is a webpage abusing the trust your browser already extends to you.

The defense that does work is the one for exactly this shape of attack: CSRF protection and clickjacking protection. So those are the two things cli2ui actually spends its security budget on:

# settings.py — even though the tool is local-only and has no sessions:
MIDDLEWARE = [
    "django.middleware.security.SecurityMiddleware",
    ...
    "django.middleware.csrf.CsrfViewMiddleware",
    # Sending X-Frame-Options: DENY so the UI can't be framed stops a
    # clickjacking page from tricking you into clicking its buttons.
    "django.middleware.clickjacking.XFrameOptionsMiddleware",
]

# Cookie-based CSRF works standalone here (no SessionMiddleware needed),
# and the trusted origins are pinned to localhost only.
CSRF_TRUSTED_ORIGINS = ["http://localhost:8000", "http://127.0.0.1:8000"]

Without CSRF, any website you visit could fire that cross-origin form POST at localhost and mutate — or drop — your database. That is the threat a local SQL console has to take seriously, and it's a per-request token, not a login. (The htmx UI sends the token via an hx-headers attribute on <body>, so there's no {% csrf_token %} sprinkled through every form.)

The rest of the hardening that still matters

Dropping auth doesn't mean dropping care. The destructive surface is real, so:

DEBUG is off by default. A Django error page leaks tracebacks, settings, and SQL — and this tool sits right next to a database. You opt into DJANGO_DEBUG=1 only when you're actually debugging.
Identifiers are bound, not formatted. Schema / table / column names go through psycopg2.sql.Identifier; the few raw-SQL spots (like an index access method) use a fixed allow-list. The ad-hoc SQL runner executes what you typed — that's the feature — but everything around it doesn't concatenate your table name into a string.
The runner defaults to read-only at the server. SET TRANSACTION READ ONLY means Postgres itself refuses writes; write mode is opt-in and snapshots the database first.

The honest part: this is a position, not a law

I'm not arguing "auth is bad." I'm arguing auth is the wrong first move for a single-user, local tool, and that the reflex to add it skips the threat model. The README says this plainly and loudly: do not expose cli2ui to an untrusted network. The moment any of these change —

more than one person uses it,
it's reachable from outside your machine or trusted network,
it's deployed as a shared service —

— the threat model flips, "a remote impostor" becomes real, and you do need accounts, encrypted credential storage, and the whole apparatus. At that point this is the wrong design. That boundary is the actual decision, stated up front, instead of bolting on a login so the architecture looks secure while the drive-by POST sails through.

(For full honesty: cli2ui stores your database connection passwords in plaintext in a local SQLite file — which is fine for a single-user local tool and indefensible the instant it isn't. Same boundary, same rule.)

"No login" isn't the absence of a security decision. It's a security decision — scoped to a threat model I wrote down, defended where the attack actually lands.

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?

I added TOTP 2FA to my Django app in ~40 lines and no 2FA library — but one line decides whether it's real

ひとし田畑 — Wed, 24 Jun 2026 10:49:20 +0000

I wanted two-factor auth on my self-hosted app, the kind where you scan a QR code
into Google Authenticator and type a 6-digit code. I reached for
django-two-factor-auth out of habit, looked at how much machinery it drags in for
a one-person tool, and backed away. The actual primitive — TOTP — is tiny:
pyotp does the math, qrcode draws the QR. The whole thing is about 40 lines.

But "about 40 lines" hides one line that decides whether your 2FA is real or pure
theater. I'll get there. First, enrollment.

Enrollment: the secret stays pending until they prove they scanned it

The naive flow is: generate a secret, save it on the user, show the QR. The bug in
that flow is subtle and mean — if the user fumbles the scan, or scans into the wrong
device, you've now flipped on 2FA with a secret they can't reproduce, and you've
locked them out of their own account.

So the secret lives in the session, not the user row, until they type a code that
proves they have it:

def totp_setup_view(request):
    import pyotp, qrcode, io, base64
    secret = pyotp.random_base32()
    request.session['totp_pending_secret'] = secret      # NOT saved to the user yet
    name = request.user.email or request.user.username
    uri  = pyotp.TOTP(secret).provisioning_uri(name=name, issuer_name='Cloud Asset Manager')
    buf = io.BytesIO(); qrcode.make(uri).save(buf, format='PNG')
    qr_b64 = base64.b64encode(buf.getvalue()).decode()
    return render(request, '_profile_totp_setup.html', {'secret': secret, 'qr_b64': qr_b64})

provisioning_uri() builds the otpauth://... URI the authenticator app expects;
qrcode turns it into a PNG; base64 inlines it straight into the page so there's no
image endpoint to wire up. Only when they come back with a working code do I persist
it:

def totp_confirm_view(request):
    import pyotp
    secret = request.session.get('totp_pending_secret')
    code   = request.POST.get('code', '').replace(' ', '')
    if not secret:
        return HttpResponse('...your session expired, start over...')
    if not pyotp.TOTP(secret).verify(code):
        return render(request, '_profile_totp_setup.html',
                      {'secret': secret, 'error': 'The verification code is incorrect'})

    profile = _get_or_create_profile(request.user)
    profile.totp_secret        = secret
    profile.two_factor_enabled = True
    profile.save()
    request.session.pop('totp_pending_secret', None)
    # ...success partial...

Prove first, persist second. The DB never holds a secret the user hasn't
demonstrated they can generate codes for.

The one line: do NOT log them in before the code

Here's the part that decides whether any of this is worth doing. At login, the
password checks out. The tempting move is to call auth_login() and then show the
code prompt. If you do that, the user is already authenticated — the 6-digit
field is a speed bump they can navigate away from. That's not 2FA, it's a decoration.

The correct shape: on a 2FA user, stash a pending marker and redirect — but leave
them anonymous:

user = authenticate(request, username=username, password=password)
if user is not None:
    if user.profile.two_factor_enabled and user.profile.totp_secret:
        request.session['totp_pending_user_id'] = user.pk     # a marker, NOT a login
        next_url = request.GET.get('next', '/')
        return redirect(f'/totp-verify/?next={next_url}')
    auth_login(request, user)                                  # only here, only without 2FA
    return redirect(request.GET.get('next') or '/')

auth_login() for the second factor happens in exactly one place — after the code
verifies:

def totp_verify_view(request):
    pending_id = request.session.get('totp_pending_user_id')
    if not pending_id:
        return redirect('/login/')                # no half-finished state to land on

    if request.method == 'POST':
        import pyotp
        code = request.POST.get('code', '').replace(' ', '')
        user    = _User.objects.get(pk=pending_id)
        profile = UserProfile.objects.get(user=user)
        if pyotp.TOTP(profile.totp_secret).verify(code):
            del request.session['totp_pending_user_id']
            auth_login(request, user)             # NOW the session is authenticated
            return redirect(request.GET.get('next') or '/')
        return render(request, 'totp_verify.html', {'error': 'Wrong code'})
    return render(request, 'totp_verify.html')

Between password and code the user holds a session key (totp_pending_user_id) and
nothing else. They can't reach any real page, because Django doesn't think they're
logged in. The if not pending_id: redirect('/login/') guard means there's no way to
land on the verify page out of order, either.

One related snag if you run gatekeeping middleware: my app forces every
authenticated request through an org-membership check, and I had to add
/totp-verify/ to its exempt prefixes — otherwise the redirect dance fights the
middleware. Worth checking your own middleware doesn't intercept the in-between step.

Turning it off should cost something

Disabling 2FA is a security downgrade, so it shouldn't be a one-click thing an
attacker on an unlocked session can do. Re-ask for the password:

def totp_disable_view(request):
    if not request.user.check_password(request.POST.get('password', '')):
        return HttpResponse('...password is incorrect...')
    profile = _get_or_create_profile(request.user)
    profile.two_factor_enabled = False
    profile.totp_secret        = ''
    profile.save()

What I'd still harden

Being honest about the edges, since the whole point of self-hosting is you own them:

The secret is stored in plaintext in a CharField. For a single-tenant, self-hosted box that's the same trust boundary as your password hashes — but encrypting it at rest (or a KMS-backed field) is the obvious next step if the DB is a bigger worry than the app server.
No recovery codes yet. Lose the phone, lose the account (short of an admin reset). One-time backup codes are the standard answer.
Clock skew: pyotp's verify() accepts only the current 30-second step by default. If users report "valid code rejected," verify(code, valid_window=1) tolerates one step either side.

Takeaways

You probably don't need a 2FA framework for TOTP. pyotp + qrcode is ~40 lines and you understand every one of them.
Keep the enrollment secret in the session until the user proves a working code, then persist. Never flip 2FA on with a secret they might not have.
The line that matters: don't auth_login() until the second factor verifies. Between password and code, the user is anonymous holding a pending marker — not logged in waiting to be asked nicely.
Gate the disable path behind a password re-entry, and watch for middleware that intercepts the in-between verify step.

This is the auth flow in a self-hosted AWS-vs-Terraform drift detector — open source
(MIT), one docker compose up: syncvey.com. Did you roll your
own TOTP or reach for django-two-factor-auth / django-otp? Curious where people
draw the build-vs-library line for auth specifically.

Streaming a pg_restore through Python without deadlocking

ひとし田畑 — Wed, 24 Jun 2026 10:39:21 +0000

You want to restore a database dump that arrived as an upload — a file object, maybe gigabytes — and you don't want to spool the whole thing to disk or load it into memory first. So you reach for the obvious thing: open pg_restore with subprocess, write the dump to its stdin in chunks, capture stderr so you can show the user a real error if it fails.

proc = subprocess.Popen(
    ["pg_restore", "-d", dbname],
    stdin=subprocess.PIPE,
    stderr=subprocess.PIPE,   # so we can report a real error
)
for chunk in iter(lambda: fileobj.read(65536), b""):
    proc.stdin.write(chunk)
proc.stdin.close()
proc.wait()

It works on your test dump. It works in CI. Then someone restores a real database and the process hangs forever — no error, no progress, both sides just... stop. Welcome to the pipe-buffer deadlock, one of the most reliable ways to wedge a subprocess pipeline.

Why it hangs

A pipe is a fixed-size OS buffer — classically around 64KB. When it fills up, whoever's writing to it blocks until someone reads the other end.

Now trace the standoff:

You're busy in a loop writing the dump to the child's stdin.
pg_restore is chewing through it and writing progress and warnings to stderr — which you said you'd capture (stderr=PIPE) but aren't reading yet, because you're stuck in step 1.
The stderr pipe fills to 64KB. pg_restore now blocks on its next write to stderr.
Blocked on stderr, pg_restore stops reading its stdin.
With nobody draining stdin, your stdin.write() fills its pipe and blocks too.

Both processes are now asleep waiting for the other to move. Nothing times out. Nothing errors. It just hangs. And it only shows up on dumps big or chatty enough to overflow that one buffer — which is exactly why it sails through testing and dies in production.

The usual advice is "use communicate()," and communicate() does solve the deadlock — by reading stdout/stderr on threads for you. But it wants you to hand it the entire input at once, which buffers your whole multi-gigabyte dump in memory. That's the thing we were specifically trying not to do. The moment you want to stream stdin and capture stderr, you're back to handling the pipes yourself.

The fix: drain stderr on a thread

You don't need a bigger buffer. You need someone reading stderr the whole time you're writing stdin. So give that job to a thread:

err_chunks: list[bytes] = []
drainer = threading.Thread(
    target=lambda: err_chunks.extend(iter(lambda: proc.stderr.read(8192), b"")),
    daemon=True,
)
drainer.start()

with proc.stdin as stdin:
    for chunk in iter(lambda: fileobj.read(65536), b""):
        stdin.write(chunk)        # stderr can never back up on us now

proc.wait(timeout=RESTORE_TIMEOUT)
drainer.join(timeout=5)

if proc.returncode != 0:
    raise EngineError(pull_cause(b"".join(err_chunks)))

The drainer reads stderr in a tight read(8192)-until-b"" loop, so the child's stderr buffer never fills. Meanwhile the main thread streams stdin to its heart's content. The with proc.stdin block closes stdin when the dump is exhausted — that EOF is how pg_restore knows the input is done — and then we wait(), join the drainer, and read the captured stderr only after the tool has exited. No buffer, no deadlock, and we still get the real error message on failure.

That's the entire trick. Everything below is the part that turns it from a snippet into something you'd trust with a database.

The details that bite

This is roughly what cli2ui's streaming restore does, and the corners worth knowing about:

Peek the first bytes to pick the tool. A custom-format archive starts with the PGDMP marker and goes through pg_restore; a plain SQL dump goes through psql. So read the leading 5 bytes, decide, then write those bytes back before the rest of the stream:

  head = fileobj.read(5)
  is_custom = head[:5] == b"PGDMP"
  ...
  with proc.stdin as stdin:
      if head:
          stdin.write(head)     # don't lose the bytes you peeked
      for chunk in iter(lambda: fileobj.read(65536), b""):
          stdin.write(chunk)

Catch BrokenPipeError. If the tool hits an error and exits while you're still writing, your next stdin.write() raises BrokenPipeError. That's not a bug to crash on — it means the child died early and the real reason is sitting in the stderr you drained. Catch it, wait(), and report from err_chunks.
Make failure mean failure. By default pg_restore limps past errors and tells you a count at the end; psql happily runs the next statement after a failed one. For a restore you want all-or-nothing, so: pg_restore --exit-on-error, and for the SQL path psql -v ON_ERROR_STOP=1 --single-transaction (the whole restore in one transaction — a failure leaves the database untouched).
Daemon thread + a join timeout. The drainer is daemon=True so a wedged reader can never keep your process alive, and wait()/join() both carry timeouts so a genuinely stuck tool gets killed instead of hanging your request forever — the very thing we set out to avoid.

The takeaway

The deadlock isn't really about Postgres — it's about pipes. Any time you stream into a child's stdin and capture its stderr (or stdout), you've signed up to read the other end concurrently, or the OS buffer will eventually introduce the two of you to a deadlock. A thread is the boring, correct answer: one side writes, the other drains, and they meet at the join.

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?

The feature that wrote a database row every minute — even when nothing happened

ひとし田畑 — Tue, 23 Jun 2026 10:33:40 +0000

My drift detector keeps a history so you can see a trend line: how much your AWS
diverged from Terraform over time, run by run. To draw that line I record a
DriftSnapshot after every scan, every tfstate import, every S3 sync.

Here's the part I didn't think through: I record one even when there's zero
drift. That's on purpose — a flat line is data too; "nothing drifted for three
weeks" is exactly what you want the chart to show. But it also means a scheduler
ticking every minute writes a row every minute, forever, whether or not anything
changed. Append-only, no ceiling. A slow-motion time bomb in a table nobody looks at.

Why I didn't notice for a while

The history view caps what it shows:

snapshots = list(DriftSnapshot.objects.filter(environment=env)[:100])

So the UI looked fine — a tidy 100 rows. Meanwhile the table underneath had tens
of thousands. The display limit was quietly hiding the growth from the one place
I'd have spotted it. The lesson that stuck: a LIMIT in your read path is not
retention. It bounds the query, not the data.

The fix has to live where the rows are born

My first instinct was a nightly cron that trims old rows. That works, but it
leaves the table unbounded between runs, and it's one more moving part to forget.
The rows all come from a single function — every scan/import/sync funnels through
the same _record_drift_snapshot(). So the cleanest place to enforce a ceiling is
right there, at the moment of creation:

snapshot = DriftSnapshot.objects.create(
    environment=environment,
    source=source,
    changed_count=len(changed),
    added_count=len(added),
    unchanged_count=unchanged,
    detail={'changed': changed, 'added': added},
)
# we append a row every run (even at zero drift) — trim the oldest beyond the cap
DriftSnapshot.prune(environment)
return snapshot

One chokepoint, so the table is bounded continuously — not "eventually, when the
cron next fires."

Keeping the newest N, per environment

The prune itself is two queries. Find the PKs of the newest N you want to keep,
then delete everything else for that environment:

@classmethod
def prune(cls, environment, keep=None):
    if keep is None:
        from django.conf import settings
        keep = getattr(settings, 'DRIFT_SNAPSHOT_RETENTION', 0)
    if not keep or keep <= 0:
        return 0  # 0 / unset = unlimited, opt out cleanly

    keep_ids = list(
        cls.objects.filter(environment=environment)
        .order_by('-detected_at')
        .values_list('pk', flat=True)[:keep]
    )
    deleted, _ = (
        cls.objects.filter(environment=environment)
        .exclude(pk__in=keep_ids)
        .delete()
    )
    return deleted

Two things I'd flag if you copy this:

Scope by environment. The cap is "newest N per environment," not N total. A global [:keep] would let a busy prod environment evict a quiet staging one's entire history.
.values_list('pk', ...)[:keep] then .exclude(pk__in=...). The slice becomes a LIMIT in SQL, so you never pull thousands of rows into Python just to decide what to delete. For per-write pruning where keep is small, that matters.

The cap is a setting, defaulting to something sane, with <= 0 meaning "unlimited"
so anyone who actually wants the full append-only log can opt out:

DRIFT_SNAPSHOT_RETENTION = int(os.getenv('DRIFT_SNAPSHOT_RETENTION', '500') or '0')

Don't forget the rows you already have

The chokepoint guard fixes new growth. It does nothing for the table that's
already bloated — those old rows were written before the cap existed and will sit
there until something deletes them. So I added a management command to backfill the
cleanup across every environment, with a dry run because deleting history deserves a
look-before-you-leap:

$ python manage.py prune_drift_snapshots --dry-run
prod:    would delete 41,902 (of 42,402)
staging: would delete 0 (of 88)
Done: would delete 41,902 snapshot(s), keeping 500 per environment.

$ python manage.py prune_drift_snapshots
prod: deleted 41,902 (kept 500)
Done: deleted 41,902 snapshot(s), keeping 500 per environment.

Same prune() underneath, just iterated over all environments — the per-write
guard and the bulk cleanup share one implementation.

One nice side effect: none of this needed a migration. It's a classmethod and a
call site, not a schema change. The existing rows are fine; they just stop being
immortal.

Takeaways

An "append-only history" feature that records even when nothing changed is unbounded by design. Decide its retention the day you build it, not the day the disk fills.
A LIMIT/[:N] in your read path bounds the query, not the table. It will happily hide unbounded growth from your own UI.
Enforce the ceiling at the single point where rows are created, so the table is bounded continuously — not at a periodic job that leaves gaps.
Ship a separate cleanup command for the rows that accumulated before the cap, and give it a --dry-run. New-growth guard and historical backfill aren't the same job.

This is the drift-history view in a self-hosted tool that tracks how your live AWS
drifts from Terraform over time — open source (MIT), one docker compose up:
syncvey.com. How do you handle append-only tables that exist
to be charted — hard cap, time-based TTL, or roll-ups into a summary table?

Try a Postgres index without HypoPG: CREATE INDEX in a transaction you roll back

ひとし田畑 — Tue, 23 Jun 2026 10:29:32 +0000

You have a slow query and a hunch: an index on (customer_id, created_at) would fix this. But you're not going to CREATE INDEX on production to find out — that takes a lock, does real work, and now you own an index you may not even want.

The usual "proper" answer is HypoPG, an extension that fakes a hypothetical index so the planner thinks it exists. It's great — when you can install it. On managed Postgres, or a prod box you don't own, or a teammate's machine, you often can't. And because the index is fake, HypoPG tells you the planner would use it but never how much faster the query actually runs — there's no real index to execute against.

There's a lower-tech trick that needs no extension and gives you real measured timing: build the index for real, inside a transaction you throw away.

The trick

BEGIN;

-- EXPLAIN ANALYZE actually runs the query — this is the "before" timing.
EXPLAIN (ANALYZE, FORMAT JSON) SELECT ... ;

-- A real index, but visible only inside this transaction.
CREATE INDEX _trial_idx ON orders (customer_id, created_at);

-- Same query again — the planner can now see the index and may pick it.
EXPLAIN (ANALYZE, FORMAT JSON) SELECT ... ;

ROLLBACK;   -- the index is never committed; it vanishes

Postgres lets a CREATE INDEX live inside a transaction, and DDL is transactional: other sessions never see the index, and ROLLBACK drops it. Between the two EXPLAIN ANALYZE runs you get the real before/after — same plan tree, same actual timing — and you can check the thing you actually wanted to know: did the planner choose your index, or ignore it?

That last part is the honest answer. Read the after-plan for an Index Scan on _trial_idx. If it's still a Seq Scan, the index wouldn't have helped — and you learned that for free, in a transaction, on whatever database you happened to have in front of you.

The one gotcha: CONCURRENTLY

You can't run CREATE INDEX CONCURRENTLY inside a transaction — Postgres forbids it. And CONCURRENTLY is exactly what you'd want in production, because it builds the index without holding a write lock on the table. So there's a split:

Inside the throwaway transaction, build a plain CREATE INDEX (non-concurrent). It briefly takes a stronger lock, but it's your own short-lived session and it's gone on rollback.
The statement you'd actually run for real should be CREATE INDEX CONCURRENTLY.

This is exactly what cli2ui's "index lab" does. It builds a plain throwaway index to measure against, but the DDL it shows you to copy is the CONCURRENTLY version:

with engine.whatif_cursor() as cur:        # autocommit=False, ALWAYS rolls back
    cur.execute(explain)                   # before
    before = parse_plan(cur.fetchone()[0])
    cur.execute(hypo)                      # plain CREATE INDEX, lives in this tx
    cur.execute(explain)                   # after
    after = parse_plan(cur.fetchone()[0])
    ddl = real.as_string(cur)              # the CONCURRENTLY version, for display
# rollback happens here — the trial index never persists
used = uses_index(after, "_trial_idx")     # did the planner actually pick it?

That whatif_cursor is a single primitive: a transaction that always rolls back (in a finally), with statement_timeout and lock_timeout pre-set so a trial can't hang or sit on a catalog/table lock.

conn.autocommit = False
try:
    with conn.cursor() as cur:
        cur.execute("SET LOCAL statement_timeout = %s", [timeout_ms])
        cur.execute("SET LOCAL lock_timeout = '2s'")
        yield cur
finally:
    conn.rollback()   # the what-if is never persisted

The honest caveats

A real CREATE INDEX does real work. Unlike HypoPG, you're actually building the index — on a big table that's slow, and that's the price of getting real timing instead of an estimate. For a huge production table, HypoPG's fake index is the better tool; for "I have this table right here, will the index help," this is the faster thing to reach for.
EXPLAIN ANALYZE executes the query. It runs inside the rolled-back transaction, so writes wouldn't persist either — but it really runs, so don't aim it at a ten-minute query without a statement_timeout.
The plain build takes a brief lock. A non-concurrent CREATE INDEX locks the table against writes for the duration. In your own short-lived transaction on a dev/staging box that's fine; it's precisely why the production DDL is CONCURRENTLY.
It's gone on rollback — which is the entire point. Nothing to clean up, nothing left behind on prod, nothing to forget about.

The thing you actually wanted — would this index change the plan, and by how much — answered on the database in front of you, with no extension to install and nothing to undo afterward.

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?

Your Django background scheduler is probably running twice. Here's why, and the migrate chicken-and-egg.

ひとし田畑 — Mon, 22 Jun 2026 11:40:12 +0000

I needed periodic background work in a Django app — scan AWS every N minutes, sync
remote tfstate, refresh data daily. No Celery, no Redis, no extra moving parts for a
one-person ops tool. django-apscheduler with a BackgroundScheduler started in
AppConfig.ready() is perfect for that scale.

And then it ran every job twice. Then it crashed on a fresh database. Both have
boring, specific causes that every "apscheduler in Django" tutorial skips, so here
they are.

Gotcha #1: `runserver` starts your app twice

AppConfig.ready() feels like "run once at startup." Under runserver it isn't —
the autoreloader forks a parent watcher and a child that actually serves, and
ready() runs in both. Start your scheduler there naively and you get two
schedulers, every job fires twice.

Django leaves you a tell: the child process has RUN_MAIN=true. So the scheduler
only starts in the child:

if 'runserver' in sys.argv:
    return os.environ.get('RUN_MAIN') == 'true'

Gotcha #2: it also starts during `migrate`, `test`, and `shell`

ready() runs for every management command. python manage.py migrate,
collectstatic, shell, and your test run all import the app, all fire ready(),
all happily spin up a background thread pool you never wanted. During tests it
pollutes state and leaks threads; during migrate it's actively dangerous (more on
that below).

So the real entry point isn't "start the scheduler," it's "should I even be allowed
to?" — a guard that inspects how the process was launched:

_NO_SCHEDULER_COMMANDS = frozenset({
    'migrate', 'makemigrations', 'collectstatic', 'shell', 'dbshell',
    'check', 'createsuperuser', 'flush', 'loaddata', 'dumpdata', ...
})

def should_start_scheduler() -> bool:
    if 'pytest' in sys.modules or 'test' in sys.argv:
        return False                       # never in tests
    if _NO_SCHEDULER_COMMANDS.intersection(sys.argv):
        return False                       # never during one-off mgmt commands
    if 'runserver' in sys.argv:
        return os.environ.get('RUN_MAIN') == 'true'   # child only
    return True                            # gunicorn / prod

ready() calls this before touching apscheduler. The principle: a process that
exists to run a migration or a shell has no business launching a recurring job
engine.

Gotcha #3: the migrate chicken-and-egg

django-apscheduler persists jobs in a database table (DjangoJobStore). But that
table is created by a migration — and on a brand-new database, ready() runs
before you've migrated. So the scheduler tries to register a job, queries a table
that doesn't exist yet, and the whole app faceplants on first boot. The migrate
guard above helps, but a fresh runserver before migrating still hits it.

The fix is to look before you leap:

from django.db import connection
if 'django_apscheduler_djangojob' not in connection.introspection.table_names():
    logger.warning("Scheduler skipped: tables not found — run migrate first.")
    return

If my own jobstore tables aren't there yet, don't start; log it and move on. The
next boot after migrate starts cleanly.

Don't let a slow tick stack up

Even with a single scheduler, a job that runs every minute but occasionally takes
longer than a minute will pile up overlapping runs. apscheduler has the knobs;
the job just has to ask:

scheduler.add_job(
    _tick, IntervalTrigger(minutes=1),
    id='boto3_scan_tick',
    max_instances=1,      # never two ticks at once
    coalesce=True,        # missed runs collapse into one, not a backlog burst
    replace_existing=True,# re-registering replaces, doesn't duplicate
)

max_instances=1 + coalesce=True means a scan that overruns just delays the next
tick instead of running concurrently with itself — which matters a lot when the tick
makes AWS API calls and writes rows.

Takeaways

AppConfig.ready() is not "once at startup." Under runserver it runs in both the reloader parent and child — gate the scheduler on RUN_MAIN == 'true'.
ready() also runs for migrate/test/shell. Guard with a should_start_scheduler() that inspects sys.argv / sys.modules, so one-off commands don't spin up jobs.
If your jobstore lives in the DB, check the table exists before registering jobs — otherwise a fresh, un-migrated database crashes on boot.
Use max_instances=1 + coalesce=True so a slow tick delays the next run instead of stacking overlapping ones.

This scheduler drives the periodic AWS drift scans in a self-hosted tool that
reconciles tfstate against live AWS — no Celery, no broker, just apscheduler in the
web process. Open source (MIT), one docker compose up: syncvey.com.
What's your Django background-job setup — apscheduler, Celery beat, or an external
cron hitting a management command?

Diffing two EXPLAIN plans as trees, not text

ひとし田畑 — Fri, 19 Jun 2026 10:50:17 +0000

You added an index, re-ran EXPLAIN, and now you want the obvious answer: did the plan actually change? So you diff the two outputs as text. And it lights up red everywhere — because cost= and rows= shift on nearly every line, even when the strategy is identical. The one line you cared about — Seq Scan on orders becoming Index Scan using orders_created_idx on orders — is buried in a wall of numeric churn.

Text is the wrong representation. An EXPLAIN plan is a tree, and the question "what changed?" is a tree-diff, not a string-diff.

Get the tree, not the text

EXPLAIN (FORMAT JSON) hands you the plan as structured data instead of pretty-printed lines. Parse it into a small node type — node kind, the table, estimated rows, cost, and children:

@dataclass
class PlanNode:
    node_type: str        # "Seq Scan", "Hash Join", "Sort", …
    relation: str | None  # the table, if it's a scan
    plan_rows: float
    total_cost: float
    children: list["PlanNode"]

Now you have two trees, before and after. The naive diff is to walk both in lockstep and compare node i to node i. That breaks the instant the planner inserts a node: add one Sort or a parallel Gather near the top and every node below it shifts position. A positional diff then reports the entire subtree as changed, which is exactly the noise you were trying to escape.

Align first, then classify

The fix is to align the two node sequences before comparing them — the same job git diff does on lines. Flatten each tree to a list, then let stdlib's difflib.SequenceMatcher find the longest matching run and the insertions/deletions around it.

The trick that makes the output readable is what you align on. If you align on the full node label, a Seq Scan on orders and an Index Scan using … on orders look like two different things — you get a delete and an add, when what actually happened is one node changed its mind. So align on a shape key that deliberately ignores the access method:

_SCANS = {"Seq Scan", "Index Scan", "Index Only Scan",
          "Bitmap Heap Scan", "Bitmap Index Scan"}
_JOINS = {"Nested Loop", "Hash Join", "Merge Join"}

def shape_key(node):
    # Any scan on `orders` keys the same, whatever the method.
    if node.node_type in _SCANS or node.node_type.endswith("Scan"):
        return ("scan", node.relation)
    if node.node_type in _JOINS:
        return ("join",)                  # any join keys the same
    return (node.node_type, node.relation)

With this key, Seq Scan on orders and Index Scan on orders both key to ("scan", "orders"), so they align into one pair. Then you compare the real labels and mark that pair changed — the flip you wanted, as a single row. A genuinely new node (a Sort that wasn't there before) has no partner and falls out as added.

The alignment itself is a dozen lines:

def diff_plans(before, after):
    fa, fb = flatten(before), flatten(after)
    keys_a = [shape_key(n) for _, n in fa]
    keys_b = [shape_key(n) for _, n in fb]
    sm = difflib.SequenceMatcher(a=keys_a, b=keys_b, autojunk=False)

    rows = []
    for tag, i1, i2, j1, j2 in sm.get_opcodes():
        if tag == "equal":               # same shape: changed-label or truly same
            for di, dj in zip(range(i1, i2), range(j1, j2)):
                rows.append(pair(fa[di], fb[dj]))
        elif tag == "delete":
            rows += [removed(fa[di]) for di in range(i1, i2)]
        elif tag == "insert":
            rows += [added(fb[dj]) for dj in range(j1, j2)]
        elif tag == "replace":
            rows += [removed(fa[di]) for di in range(i1, i2)]
            rows += [added(fb[dj]) for dj in range(j1, j2)]
    return rows

Every node ends up in one of four buckets — same / changed / added / removed — and the strategy flips you actually care about stop hiding behind shifting cost numbers.

One more deliberate choice: don't put tree depth in the shape key. Inserting a wrapper node bumps the depth of everything beneath it; if depth were part of the key, that one insertion would re-diff the whole subtree. Leaving depth out lets the sequence alignment keep the rest paired up, and you still carry depth along just for indentation when you render.

The honest caveat

Shape-key alignment is a heuristic for human eyeballing, not a proof. A self-join — two scans of the same table — keys identically, so the two can mis-pair; difflib aligns by sequence order, so an unusual reordering can put the wrong nodes together. It's right the vast majority of the time and it makes "what changed?" legible in a glance, but if it ever looks wrong, fall back to the full node detail. And remember plan_rows/total_cost are estimates — only EXPLAIN ANALYZE gives you real counts and timings to diff.

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. The plan-tree diff above is the real plan_diff.py it uses for "before vs after an index." What command do you reach for that should be a button?

Your runtimes have an expiry date. I baked the EOL calendar into the app so it works offline.

ひとし田畑 — Fri, 19 Jun 2026 10:48:27 +0000

Every runtime and piece of middleware you run has a quiet expiry date. PHP 8.0
went EOL in late 2023. Node 16 in 2023. Postgres 12 in 2024. After that: no
security patches. The problem is never "we didn't know EOL was a thing" — it's that
the EOL calendar lives in a dozen vendor pages and nobody checks them until an
auditor does.

I wanted my self-hosted ops tool to flag this automatically. The obvious move is to
call an API like endoflife.date. But I made the default
offline: the EOL calendar is baked into the app, and the external fetch is
strictly opt-in. Here's why, and the part that was actually fiddly — version
matching.

Offline by default: a self-hosted tool shouldn't phone home

If you ship a tool people run inside their own infra, "calls an external API on
startup" is a liability, not a feature. Air-gapped environments break. Security
reviewers ask why your inventory tool is making outbound requests. So the EOL data
is a plain dict compiled into the code:

# {canonical_name: {cycle: eol_date_or_None}}   None means "actively supported"
_EOL = {
    'python': {
        '3.7': date(2023, 6, 27),
        '3.8': date(2024, 10, 7),
        '3.9': date(2025, 10, 5),
        '3.11': None,
        '3.12': None,
    },
    'postgresql': {
        '12': date(2024, 11, 14),
        '13': date(2025, 11, 13),
        '16': None,
    },
    # redis, php, nodejs, nginx, mysql, ruby, go, java, ...
}

It works the moment you docker compose up, no network required. If you want
fresher data, you flip EOL_REFRESH_ENABLED=true and a daily job pulls from
endoflife.date and overlays the result on top of the baked-in dict:

def _effective() -> dict:
    eff = {k: dict(v) for k, v in _EOL.items()}   # start from the offline baseline
    snap = _load_snapshot_data()                  # latest fetched snapshot, if any
    if snap:
        for product, cycles in snap.items():
            eff.setdefault(product, {}).update(cycles)
    return eff

Offline data is the floor; the network is an enhancement, never a dependency. (The
fetch side validates the product slug against a regex and only ever talks https to
endoflife.date — an inventory tool making arbitrary outbound requests is exactly the
SSRF footgun you don't want to ship.)

The fiddly part: matching a version string to a cycle

Here's where it stops being a lookup table. A dependency reports its version as a
string — "3.11.4", "8", "1.24.2" — but the EOL calendar is keyed by release
cycle: "3.11" for Python, "8" for MySQL. You can't just dict[version].

The rule that works: try the longest cycle key first (major.minor), then fall back
to major only.

for n_parts in (2, 1):                      # "3.11.4" → try "3.11", then "3"
    cycle = _major_minor(version, n_parts)
    if cycle not in cycles:
        continue
    eol_date = _parse_date(cycles[cycle])
    if eol_date is None:
        return 'ok'                         # cycle exists, no EOL date = supported
    if eol_date < today:
        return 'eol'
    if eol_date < today + timedelta(days=180):
        return 'warning'                    # within 6 months
    return 'ok'
return 'unknown'

Python is tracked at major.minor (3.11 ≠ 3.12), but MySQL "8.0.36" should match
the "8.0" cycle and Node "20.11.1" should match "20". Longest-match-first gets
both right without per-product special-casing. The four states — eol / warning /
ok / unknown — matter too: unknown is not ok. "I have no data for this"
and "this is supported" are different answers, and collapsing them is how you ship a
green dashboard that's quietly lying.

The "this has no EOL" case

Not everything has an end-of-life. Gunicorn, Celery, Composer — there's no vendor
EOL cycle to track. Naively those show up as unknown forever, which is noise. The
trick is an alias table that can map a name to None meaning "no EOL concept":

_ALIASES = {
    'node':     'nodejs',        # normalize spelling
    'postgres': 'postgresql',
    'gunicorn': None,            # explicitly: no formal EOL — don't nag about it
    'celery':   None,
}

def canonical(name):
    return _ALIASES.get(_normalize(name), _normalize(name))

So canonical() does double duty: it normalizes spellings (node → nodejs) and
encodes "we deliberately don't track this" as None, which short-circuits to
unknown and keeps it out of the warnings. Suppressing a check on purpose is a
feature; the alias table is where that intent lives.

Takeaways

For a self-hosted tool, make EOL data offline-first: bake in a baseline dict, make the external refresh opt-in, and overlay fetched data rather than depend on it. It runs air-gapped and doesn't surprise a security reviewer.
Don't index the EOL table by raw version. Match longest cycle first (major.minor → major) so 3.11.4, 8.0.36, and 20.11.1 all land on the right cycle.
Keep unknown distinct from ok, and give yourself a way to say "this has no EOL" on purpose so the signal stays clean.

This EOL check is one layer of a self-hosted tool that inventories your AWS assets
and the apps/middleware running on each environment, then flags drift and aging
runtimes. Open source (MIT), one docker compose up: syncvey.com.
How do you track middleware/runtime EOL today — a spreadsheet, Dependabot, or hope?

DEV Community: ひとし 田畑

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

Part 1 — Not all drift is equal

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

Where it lives: a detachable plugin

Takeaways

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

The bit lives in pg_attribute

Why now: PostgreSQL 18 flips the default

The same hole in MySQL

The honest part

My dashboard showed every number and helped you decide nothing

The three layers, and where mine stopped

The embarrassing part: I had already built layer 2

The hero band

The layout gotcha: the hero kept disappearing

Freshness and the empty state

What I deliberately left undone

Takeaways

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

The one pattern

Forms are just buttons that POST

Where Alpine earns its keep

The honest part: where this breaks

I shipped a database console with no login — on purpose

What auth would actually cost

The real threat: the tab you forgot about

The rest of the hardening that still matters

The honest part: this is a position, not a law

I added TOTP 2FA to my Django app in ~40 lines and no 2FA library — but one line decides whether it's real

Enrollment: the secret stays pending until they prove they scanned it

The one line: do NOT log them in before the code

Turning it off should cost something

What I'd still harden

Takeaways

Streaming a pg_restore through Python without deadlocking

Why it hangs

The fix: drain stderr on a thread

The details that bite

The takeaway

The feature that wrote a database row every minute — even when nothing happened

Why I didn't notice for a while

The fix has to live where the rows are born

Keeping the newest N, per environment

Don't forget the rows you already have

Takeaways

Try a Postgres index without HypoPG: CREATE INDEX in a transaction you roll back

The trick

The one gotcha: CONCURRENTLY

The honest caveats

Your Django background scheduler is probably running twice. Here's why, and the migrate chicken-and-egg.

Gotcha #1: runserver starts your app twice

Gotcha #2: it also starts during migrate, test, and shell

Gotcha #3: the migrate chicken-and-egg

Don't let a slow tick stack up

Takeaways

Diffing two EXPLAIN plans as trees, not text

Get the tree, not the text

Align first, then classify

The honest caveat

Your runtimes have an expiry date. I baked the EOL calendar into the app so it works offline.

Offline by default: a self-hosted tool shouldn't phone home

The fiddly part: matching a version string to a cycle

The "this has no EOL" case

Takeaways

DEV Community: ひとし田畑

Gotcha #1: `runserver` starts your app twice

Gotcha #2: it also starts during `migrate`, `test`, and `shell`