The alert email: production server root partition at 81% (30G/40G, past the 80% red line). First instinct: check docker system df — and there it is, images at RECLAIMABLE 100% (7), apparently a quick win. Except all 7 images are running; following the hint with docker image prune -a would be a production incident.

Encountered this while building AI Analytics — an LLM-powered analytics platform that surfaces market trends, user behavior, and sales data; the disk in question belongs to the Docker server running its Airflow data pipeline.

TL;DR​

Past a disk red line, don't trust docker system df RECLAIMABLE — it's an estimate of "space with no container reference", and active images get flagged 100% anyway. The right move: sudo du -xh -d1 / layer by layer. This incident's three invisible hogs were none of them business data: task logs accumulating in container writable layers (no log volume in the Dockerfile), package-manager caches (npm + pnpm, ~4G combined), and a backup script that never pruned (full clones every run). Fixes: delete caches, reclaim writable layers with force-recreate, add TTL pruning to the backup script — 81% back down to 64%.

Symptoms​

$ df -h /
Filesystem      Size  Used Use% Mounted on
/dev/vda1        40G   30G  81% /

$ docker system df
TYPE        TOTAL   ACTIVE   SIZE     RECLAIMABLE
Images      7       7        4.2GB    100% (7)    ← all running
Containers  5       5        810MB    0%

Acting on docker system df (clean images) is a dead end — RECLAIMABLE says 100% but ACTIVE says 7/7. Where the space actually went, docker system df never shows:

sudo du -xh -d1 / | sort -rh | head
# selected output:
# 2.7G    /root/.npm              ← npm cache
# 1.1G    /root/.local/share/pnpm ← pnpm store
# 857M    /root/backups-git       ← backup clones, never pruned
# (hidden in overlay2: airflow scheduler writable layer 468M + dag-processor 257M)

Root Cause​

Three kinds of consumption, all invisible from the "business data" perspective.

Writable layers eating logs. Airflow task logs are written inside the containers with no external volume — scheduler writable layer 468M, dag-processor 257M, ~780M accumulated in two weeks, monotonically growing. Image layers never change; the writable layer does. docker system df buries it in the Containers SIZE total (810M), where it has no presence.

Package-manager caches only grow. On a server with frequent deploys/builds, ~/.npm (2.7G) and the pnpm store (1.1G) pile up indefinitely; nobody ever cleans them.

A backup script that never prunes. The backup script clones the repo worktree in full and pushes to GitHub every run — old clone directories are never removed, so 857M is mostly historical duplicates. Sneakier: orphan clones whose branch never pushed successfully can't just be deleted; verify first.

And docker system df RECLAIMABLE is a statistical estimate of "space not referenced by a running container" — active images can display 100% reclaimable (all 7 of ours did). It's a false-positive generator, not a cleanup guide.

Solution​

Step 1: Locate with du, layer by layer — look before deleting​

sudo du -xh -d1 / | sort -rh | head        # root partition, level by level
sudo du -xh -d1 /var/lib/docker | sort -rh | head   # drill into Docker's dir
docker ps -as --format "table {{.Names}}\t{{.Size}}"  # per-container writable layer

du -x stays on one filesystem, avoiding /proc, /sys noise and overlay confusion; docker ps -as SIZE exposes each container's writable layer — the key command for the "logs written inside the container" family.

Step 2: Delete caches outright​

npm cache clean --force        # or simply rm -rf ~/.npm/_cacache
pnpm store prune               # removes only unreferenced packages

~5.1G reclaimed, zero risk — caches re-download on demand.

Step 3: Reclaim writable layers with force-recreate​

docker compose up -d --force-recreate   # writable layer goes away with the old container

~780M reclaimed here. Two preconditions: pick a low-traffic window (services restart), and rescue anything valuable first — e.g. docker cp the task logs out, or they vanish with the container.

Step 4: TTL for backups, prevent recurrence​

# keep backup dirs for 7 days, prune older ones
find /root/backups-git -maxdepth 1 -type d -mtime +7 -exec rm -rf {} +

Add the pruning to the tail of the backup script (deployed in our github-backup-push.sh); before deleting, check for orphan clones whose branch never pushed (git ls-remote to compare) and confirm no unique commits — our 325M of orphans were removed only after verification.

After cleanup: 81% → 64%, with caches + pruning + writable layers together reclaiming ~6.7G.

FAQ​

docker system df shows 100% RECLAIMABLE — can I just delete?​

No. RECLAIMABLE estimates "space with no container reference", and running active images can still be flagged 100% reclaimable — all 7 of ours were. It answers "how much is theoretically unreferenced", not "what can be deleted". Measure first with du -xh -d1 and docker ps -as.

What's the difference between docker ps SIZE and docker system df SIZE?​

docker ps -as SIZE is the per-container writable layer; docker system df is the category-level aggregation over images/containers/volumes/cache. Logs accumulating inside a container only ever appear in the writable layer (visible via docker ps -as), never in any image size — which is exactly why "images look small but disk keeps growing".

How do I clean up a Docker server running out of disk?​

Three categories: package-manager caches deleted outright (npm cache clean --force, pnpm store prune), zero risk; container writable layers reclaimed via docker compose up -d --force-recreate (service restart; rescue logs first); backups, logs, and clone directories put on TTL pruning to prevent recurrence. Before any deletion, confirm the hogs with du -xh -d1 /.

After the "crowd asset" collection feature of our browser extension shipped, support started hearing the same story: some users open crowd assets mid-month and see "already up to date" — while their data stops at the 1st. The un-upgraded old extension had stopped collecting, and would only resume on the first day of the next month.

Encountered this while building e-commerce data collection tooling — a one-stop e-commerce operations solution from data collection to smart analytics; the collection flag is served by the backend, and old and new extension versions share the same server-side table.

TL;DR​

The new release gave the existing boolean flag is_current_month a new meaning ("this month's collection window is covered"); un-upgraded clients read it with the old meaning ("data is current"), their idempotency check short-circuited, and collection stopped mid-month. This is not a bug to fix — it's version skew: one field, two interpretations. The impact has a natural self-healing boundary (the flag flips false next month); the response is upgrade prompts + idempotent upsert (no rollback), and the prevention is: semantic changes must come with a new field.

Symptoms​

New extension collects: writes the current-month row, is_current_month = true

Old extension syncs:    reads that row → hits the "up-to-date" check → stops collecting
User's view:            mid-month, "crowd assets" says already current; data frozen at month start
Next month, day 1:      is_current_month flips false → old version resumes (self-heals)

The eerie part: server data is perfectly correct, the new extension works, the old extension's code never changed — the only failure mode is "old version reading rows written by the new version".

Root Cause​

Textbook version skew. The flag's name stayed, its meaning moved: to the new release it means "current-month window covered"; the old release, following its own historical semantics, reads the same row as "data is current" — and its perfectly-correct idempotency check ("already current → skip re-collection") short-circuits the whole collection. Both sides' logic is right; the wrong part is letting two semantics share one field.

Client (and every client-side) version distribution is outside the server's control: after a release, old and new versions coexist for weeks as the norm. Any change to a server-side field's meaning is read by every historical version — the same lesson as the classic feature-flag mistake: re-purpose an old flag to carry a new meaning, and readers act on the old meaning.

Solution​

Immediate response: prompt upgrades + idempotent writes​

When releasing the version with the new collection logic, explicitly ask users to upgrade — the old version will not recover on its own within the month. Server and data need no rollback: collection writes are idempotent upserts, so interleaved old/new writes produce no dirty data, and everything realigns when the flag flips next month.

Long-term prevention: new semantics, new field​

Move the "window semantics" off the boolean onto a new field with an explicit window; old clients that don't know the field can't misread it:

// anti-pattern: old flag re-purposed for new semantics
{ "is_current_month": true }

// correct: new semantics in a new field, explicit and comparable
{ "collected_window": "2026-08", "source_version": "2.3.0" }

The idempotency key decouples from business semantics: old versions judge by the old field, new versions by the new one, no cross-contamination.

Design principle: give every flag a self-healing boundary​

Prefer binding flags to natural time boundaries (month, day) rather than absolute semantics like "current". The month boundary here capped the worst case at one month; without such a boundary, skew is permanent and only a release can fix it.

FAQ​

What are the best practices for feature flags?​

One flag, one meaning. New semantics get a new field, not a re-purposed old flag. Bind flags to explicit time windows or versions. Before changing any meaning, enumerate every reader — especially un-upgraded clients — and confirm they won't interpret new values with old semantics. The mid-month collection stop here is the direct consequence of re-using an old flag.

How do I keep server-side fields backward compatible?​

Add, never mutate: existing fields keep their meaning, type, and behavior; new semantics go into new fields; old clients ignore what they don't recognize. Keeping a field's name while changing its meaning is a breaking change to every existing reader.

What should old clients do when they read data with new semantics?​

Three steps: bound the impact to a self-healing window (a calendar-month flip restores collection automatically); prompt upgrades at release to shorten the overlap window; keep server writes idempotent (upsert) so interleaved old/new writes blend safely — no rollback, no data cleanup.

The first production run of an LLM batch validation job finished green — status success, no errors. But the output told another story: all 2,449 items to validate were tagged with the fallback marker, and the actual LLM call count was zero. A feature shipped as "semantic validation on by default" had never once run.

Encountered this while building AI Analytics — an LLM-powered analytics platform that surfaces market trends, user behavior, and sales data; this job runs in the title-optimization stage of its data pipeline.

TL;DR​

The capacity gate is all-or-nothing: if the workload exceeds the cap, the entire batch degrades to fallback with zero LLM calls — and the job still reports success. The trial cap was 400 while production actually had 2,449 keyword×product pairs. Two lessons: calibrate capacity limits against measured production scale, and degrade at unit granularity (per group / queue / truncation) with abnormal fallback rates made observable.

Symptoms​

Stage 2 of the pipeline is LLM semantic validation, guarded by a capacity gate at the entrance:

def semantic_validate(pairs, cap=400):
    if len(pairs) > cap:
        # over the cap: degrade the whole batch, not a single LLM call
        return [mark_overflow(p) for p in pairs]
    return [llm_validate(p) for p in pairs]

First production run:

keyword×product pairs: 2449/2449 all marked validation_mode='overflow'
actual LLM calls: 0
job status: success (no errors at all)

Judging by "did it finish", everything looks fine; only the distribution of the output column reveals the feature was disabled wholesale.

Root Cause​

Two problems stack up. The number: the trial cap was 400, but production scale — 60 market keywords × same-category products plus 50 own-store keywords × products across 92 products — pushed the pair count to 2,449. Off by an order of magnitude. The structure: the gate is all-or-nothing — over the cap means the whole batch degrades. What was meant as a capacity constraint effectively became "over the limit = feature off", and the degradation landed silently in a data column: no exception, no log line.

This is the same family as DeepSeek thinking consuming the output budget and silently returning empty: the fallback logic digests the failure, and the surface always says success.

Solution​

Step 1: Calibrate the cap against measured production scale​

Count the real workload before launch; don't estimate:

# dry-run: measure the scale, zero LLM calls
python -c "from pipeline import build_pairs; print(len(build_pairs(shop='prod')))"

Measured 2,449 → set the cap to 3,000 (roughly 1.2-2x headroom), and confirm an oversized shop still has a degradation path instead of hitting a wall.

Step 2: Switch the granularity from "total" to "grouped"​

Call per product group so call counts grow linearly with product count instead of exploding with the keyword×product product:

def semantic_validate(pairs, cap):
    groups = group_by_product(pairs)          # 92 products → ~92 calls/round
    results = []
    for g in groups:
        if within_budget(g, cap):             # per-group check, no wholesale give-up
            results.extend(llm_validate(g))
        else:
            log.warning("capacity gate: group degraded",
                        extra={"size": len(g), "cap": cap})
            results.extend([mark_overflow(p) for p in g])
    return results

After calibration the third run showed 2,449/2,449 going through LLM validation; larger shops now degrade per group instead of losing everything.

Step 3: Make degradation observable​

Instrument the fallback path and alert on abnormal ratios (e.g. fallback rate > 50%). Degradation is a safety net, not a cover — it should be seen, not silently absorb the over-limit condition for you.

FAQ​

How should a fallback mechanism in an LLM pipeline be designed?​

Keep the granularity small — degrade per item or per group instead of abandoning the batch; leave traces (marker columns, logs, metrics) and configure alerts. An all-or-nothing gate, once triggered, equals switching the feature off; it only fits hard-cost-ceiling scenarios with explicit alarms.

How do I set a capacity limit for LLM batch jobs?​

Don't guess. Run a dry-run on production-scale or proportionally sampled data to measure the actual workload, set the limit at 1.5-2x the measured value, and revisit it as the business grows. An estimate off by an order of magnitude is the standard setup for this incident.

How do I detect that an LLM job was silently degraded?​

The job status usually still says success. Inspect the output: count the share of fallback markers and check whether actual LLM calls match expectations. Alert on abnormal fallback rates (especially 100%) to turn silent failures into explicit signals.

While deploying a multi-package project (frontend and backend sharing one repository), npm audit reported 3 high vulnerabilities in the deploy log. We logged them against the backend — only to confirm later that all 3 highs lived in the frontend tree, and the backend had been a separate set of moderates all along.

Encountered this while building AI Analytics — an LLM-powered analytics platform that surfaces market trends, user behavior, and sales data; the frontend lives at the repository root and the backend in server/, deployed separately from one repo.

TL;DR​

npm audit summary lines carry only counts, no path. When a deploy pipeline runs npm install in several directories in sequence, the outputs interleave and the summaries become unattributable. The fix: use each package tree's unique fingerprint — the N in audited N packages — to attribute findings first, then pin the vulnerable transitive dependencies with overrides in package.json. A second deploy brought both sides to zero.

Symptoms​

The deploy script runs npm install in the frontend (repo root) and then the backend (server/), and both outputs land in the same log stream:

# Deploy log (summary lines carry no path)
added 546 packages in 41s
found 3 high severity vulnerabilities

added 372 packages in 24s
found 4 moderate severity vulnerabilities

Our handover notes attributed the "3 high" to server/. Chasing the backend dependency chain led nowhere — the server/ audit, run locally or on the server, consistently showed 4 moderates and never a single high. "Visible in the log, unattributable in practice" is a chronic disease of mixed deploy pipelines; our earlier write-up on stale build artifacts after deployment is the same family of problem.

Root Cause​

The npm audit summary only prints "found X vulnerabilities" with no directory info, and the adjacent audited 546 packages rarely registers as an attribution clue. Two package trees with wildly different sizes (546 vs 372) turn out to be the only stable fingerprint.

The frontend root installs the full Vite + React + Ant Design Pro stack, so its tree is large; @ant-design/pro-components → @ant-design/pro-layout pulls in an old path-to-regexp, which is exactly where the 3 highs came from. The backend server/ is a lean Express + tsx tree whose only issue is the tsx → @esbuild-kit/core-utils → old esbuild chain of moderates.

Solution​

Step 1: Attribute findings by audited N​

Run npm install in each directory locally (or read added N packages straight from the deploy log) and record the tree sizes:

cd <repo-root> && npm install 2>&1 | tail -2   # added 546 packages ...
cd server      && npm install 2>&1 | tail -2   # added 372 packages ...

In the deploy log, found 3 high immediately follows added 546 → frontend; 4 moderate follows added 372 → backend. Get attribution right before touching any dependency.

Step 2: Expand the vulnerability chain​

npm audit                # see the Path field, full chain
npm ls path-to-regexp    # or reverse-lookup who depends on a package

The frontend output confirmed the chain: @ant-design/pro-components → @ant-design/pro-layout → path-to-regexp (old version, 3 high).

Step 3: Pin the transitive dependency with overrides​

Frontend root package.json:

{
  "overrides": {
    "path-to-regexp": "^8.4.2"
  }
}

Backend server/package.json (with tsx bumped to a newer release):

{
  "overrides": {
    "@esbuild-kit/core-utils": {
      "esbuild": "^0.25.12"
    }
  }
}

overrides supports nested syntax, scoping the pin to the child dependency under one specific parent — more surgical than overriding a package name globally.

Step 4: Reinstall and verify​

rm -rf node_modules package-lock.json && npm install && npm audit

After redeploying both sides, audit reports 0 vulnerabilities on each.

FAQ​

Why is npm audit fix not working?​

npm audit fix only upgrades versions inside the allowed semver range. When the vulnerability lives in a transitive dependency whose version is pinned by an upstream package's range, fix cannot touch it. Use overrides in package.json to force the version, then reinstall.

How do I find which dependency chain an npm audit finding comes from?​

Read the Path field in the full npm audit output — it lists the complete chain from a direct dependency down to the vulnerable package. You can also reverse-lookup with npm ls <package>. Summary lines carry counts only.

How do I map npm audit results to a specific subproject?​

Deploy log summaries carry no directory name, so match them by each directory's added N packages / audited N packages tree size. Run npm install once per directory locally, record each N, and the mapping stays stable.

Auditing a production task's decision snapshot, we found stage 5's feature data sitting in array slot 3 instead of the documented slot 4. Downstream consumers and the detail drawer read by "segment number minus one" — and were silently getting the previous stage's output.

Encountered this while building AI Analytics — an LLM-powered analytics platform that surfaces market trends, user behavior, and sales data; the snapshot is what the pipeline leaves behind for frontend rendering and post-hoc audit.

TL;DR​

The pipeline appends each stage's output into the snapshot array in execution order, and an if rows.empty: skip guard drops 0-row segments entirely, shifting every later segment forward. The static mapping "segment number − 1 = slot" breaks at will, and which segments are empty depends on runtime state — slots differ per run. Two fixes: resolve slots at read time by in-row keys (recommended), or keep placeholders for empty segments so slots stay constant.

Symptoms​

The assembly logic looks like this:

snapshot = {"features": [], "rule_output": []}
for seg in segments:                    # stages 1..5 run in order
    df = execute_sql(seg.sql)
    if not df.empty:                    # 0-row segments dropped here
        snapshot["features"].append(df.to_dict("records"))

The design assumption was "stage 5 → slot 4". Production snapshot audit found stage 5's features in slot 3:

all segments populated:   ①→0  ②→1  ③→2  ④→3  ⑤→4   ✓ matches assumption
stage 4 empty, dropped:   ①→0  ②→1  ③→2       ⑤→3   ✗ shifted
stages 3+4 empty:         ①→0  ②→1               ⑤→2   ✗ shifted again

The same code version produces completely different slot layouts depending on shop and permissions — an empty whitelist table drops stage 4; a disabled feature flag drops stage 3. Slots drift with runtime state.

Root Cause​

Positional addressing collided with sparse assembly. The snapshot array is a runtime product of concatenating "segments that produced output" — a sparsely-filled collection compressed. Consumers hard-coding "segment − 1" implicitly assume every segment always yields at least one row. That assumption shatters in three routine situations: empty whitelist tables, disabled feature flags, naturally empty business data. 0-row segments are the norm, not the exception.

Deeper down, the emptiness guard itself (if not df.empty) is not wrong — wrong is the contract's implicit premise. The design doc says "slot = segment − 1" but nobody declared it as an explicit contract. Every position-addressing consumer inherits an unacknowledged, unmaintained assumption.

Solution​

Option A (recommended): resolve segments by in-row keys at read time​

Let every row carry a segment identifier key; consumers resolve positions at read time with no static mapping:

def locate_segment(features: list, seg_key: str) -> dict:
    for row in features:
        if seg_key in row:              # row carries its own identity
            return row
    raise KeyError(f"segment '{seg_key}' missing in snapshot")

Slot drift becomes irrelevant — you look for "the segment whose keys look like this", not "element N". The one requirement: all consumers go through this single resolution entry point (write it into the processor docstring and the consumer contract: no hard-coded slots, ever).

Option B: keep placeholders for empty segments on the write side​

If downstream can't change yet, keep "slot = segment number" constant at assembly time:

snapshot["features"].append(
    df.to_dict("records") if not df.empty else {"__empty__": True}
)

The cost: placeholder objects appear in the snapshot and every consumer must handle them. Fine as a transition; converge on Option A long-term.

Step 3: contract tests over multiple runtime states​

Build snapshot fixtures for "all populated / one empty / several empty" and assert consumers parse all three identically. Testing only the all-full scenario tests nothing.

FAQ​

How do you handle schema drift in data engineering?​

Replace positional contracts with key-based ones: address snapshots, messages, and interfaces by field name or segment identifier, never array index. When upstream changes without notice (empty segments skipped, fields added or removed), key-addressed consumers at worst raise "not found" — they never silently read the wrong data.

What is the difference between schema drift and schema evolution?​

Evolution is explicitly managed versioning: add a field, cut a release, migrate consumers. Drift is passive: upstream changes and downstream quietly misaligns. The slot shifting in this post is textbook drift — nobody touched the contract; the data shape changed.

How do I detect this kind of slot misalignment in a pipeline?​

Two layers: contract tests covering multiple runtime states (all populated / one empty / several empty) asserting identical consumer parsing; and periodic production snapshot audits checking each slot's content against its declared segment. "Content and position disagree" is drift.

Chasing a monitoring gap: server-monitor filters alert-grade logs by the level name warn, but 38 rows in the shared logs table carried level='warning'. The filter didn't match by one character — and in the monitor's eyes, those 38 rows did not exist.

Encountered this while building AI Analytics — an LLM-powered analytics platform that surfaces market trends, user behavior, and sales data; server-monitor is its alerting module, consuming one logs table written by four services.

TL;DR​

The cross-language logging contract defines lowercase warn/fatal; Python's stdlib record.levelname produces WARNING/CRITICAL — written raw or with a bare .lower() you get warning/critical, which contract-name filters never match. Two principles fix it: normalize at a single point in the write boundary (WARNING→warn, CRITICAL/FATAL→fatal) so no consumer ever has to juggle spellings; and write the contract doc as the intended implementation, not a snapshot of the current one — this drift survived so long precisely because the contract's Python column documented the buggy code.

Symptoms​

Four services write one logs table; the contract specifies level values: debug / info / warn / error / fatal. A reconciliation query:

SELECT service, level, count(*)
FROM logs
GROUP BY service, level ORDER BY 1, 2;

turns up spellings that don't exist in the contract:

 service    | level    | count
------------+----------+-------
 ai-dag     | warning  |    21   ← not in the contract
 rag-service| warning  |    17   ← not in the contract
 ...        | warn     |   ...   ← the actual contract name

The monitor filters level = 'warn'; these 38 alert-grade rows silently vanish.

Root Cause​

Layer one is literal mismatch: Python's stdlib levels are DEBUG / INFO / WARNING / ERROR / CRITICAL — there is no WARN (a deprecated alias) and no FATAL. Both services pushed record.levelname into the table: one raw (uppercase WARNING), one lowercased (warning). Neither matches the contract's warn.

Layer two is the one worth losing sleep over: the contract document itself specified the wrong implementation. In the cross-service contract's field table, the Python services' level column literally read "record.levelname" and "record.levelname.lower()". The doc was describing reality instead of prescribing it — so the buggy implementations carried the contract's endorsement, and nobody questioned them. This is the same harm shape as try/except swallowing exceptions into silent failures: nothing crashes, things just quietly go missing — by the time anyone looks, dozens of alert rows were never seen.

Solution​

Step 1: Single-point mapping at the write boundary​

Each service defines one normalization function; every write path (formatter and DB sink) goes through it:

_LEVEL_NAME_MAP = {"WARNING": "warn", "CRITICAL": "fatal", "FATAL": "fatal"}

def normalize_level(levelname: str) -> str:
    """WARNING→warn, CRITICAL/FATAL→fatal, everything else lowercased."""
    return _LEVEL_NAME_MAP.get(levelname.upper(), levelname.lower())

payload = {"level": normalize_level(record.levelname)}   # always emits a contract name

The keyword is "single point": the JSON formatter and the DB handler share one function, so the mapping changes in exactly one place and no second implementation can appear.

Step 2: Rewrite the contract doc as the intended implementation​

The field table's Python columns now read normalize_level(record.levelname), and a new "level name mapping" section documents the rules, the anti-patterns (no raw writes, no bare lowercasing), and each service's function entry point. A contract is a spec — not a snapshot of whatever happens to be deployed.

Step 3: Add a reconciliation query so drift is discoverable​

SELECT level, count(*) FROM logs
WHERE service IN ('ai-dag', 'rag-service')
GROUP BY level ORDER BY 2 DESC;

Any spelling besides warn is drift. This query belongs in routine inspection, turning "contract vs implementation" from a verbal promise into an assertable check.

Step 4: Clean up存量 (optional)​

New writes no longer produce off-contract names; handle the existing 38 rows as needed:

UPDATE logs SET level = 'warn' WHERE level = 'warning';

Small volumes can be left to age out; large ones, or anything feeding historical statistics, deserves the UPDATE.

FAQ​

Why can't Python's WARNING be written straight into the logs table?​

The stdlib literals are WARNING/CRITICAL, while cross-language contracts define warn/fatal. Raw or lowercased values (warning/critical) are unknown levels in contract-land — every consumer filtering by contract names silently drops them. The Python side must map at the write boundary.

Are WARNING and WARN the same level?​

Same semantics, different literals. Python logging has no WARN level (deprecated alias; the emitted name is always WARNING) and no FATAL (it's CRITICAL). That's why lowercasing doesn't help — you need an explicit mapping: WARNING→warn, CRITICAL→fatal.

How do I detect that a logging contract and its implementation have drifted?​

Periodically reconcile by contract level names (GROUP BY level); any off-contract spelling is drift. More importantly, the contract doc should specify the intended implementation and name the mapping function — when the doc copies the current implementation, the bug gets an endorsement, which is exactly why this drift survived so long.

While aggregating daily ad data into weekly reports, the ratio metrics — PPC, CPM, ROI — jumped by tens of times: PPC went from 4.70 in the daily detail to 111.39 in the weekly report.

Encountered this while building AI Ops — LLM-powered analysis that surfaces market trends, user behavior, and sales insights to drive precise operations strategy. The ad data API returns daily detail rows, which must be aggregated to week granularity before feeding reports. The first check after launch: PPC 111.39 (daily actual 4.70), CPM 1585.9 (daily actual 68.6), ROI 169.98 (daily actual 1.82) — all 13 ratio/average columns distorted.

TL;DR​

Applying SUM directly to ratio/average columns like CTR, PPC, ROI in an aggregation query yields "the sum of daily ratios," not a mean — values inflate proportionally to the number of days aggregated. Two rules: aggregate only additive columns (impressions, clicks, cost — the totals), skipping every ratio column; recompute ratios from the totals after aggregating (cost ÷ clicks, clicks ÷ impressions). If you only have ratios without the underlying totals, take a weighted average using the denominator as weight — never a plain average.

The Symptom​

The aggregation code SUMmed "every numeric column," silently sweeping ratio columns along:

-- Wrong: SUM every column
SELECT
  campaign_id,
  date_trunc('week', day) AS week,
  SUM(clicks)      AS clicks,
  SUM(impressions) AS impressions,
  SUM(ppc)         AS ppc,   -- sum of 7 daily PPC values!
  SUM(ctr)         AS ctr,   -- sum of 7 daily CTR values!
  SUM(roi)         AS roi    -- sum of 7 daily ROI values!
FROM daily_ad_report
GROUP BY campaign_id, date_trunc('week', day);

Measured comparison (one campaign, one week):

No errors anywhere: data ingested normally, reports rendered normally — only by comparing the weekly report against daily details side by side could you see values off by one to two orders of magnitude.

Root Cause​

Ratios and averages are non-additive derived quantities. Each day's ppc = cost / clicks has a different denominator; adding 7 daily PPC values mathematically yields "a sum of 7 relative values," which has no business meaning. CTR and ROI are the same.

"Sum all numeric columns" is a silent trap. Aggregation code usually loops over columns uniformly, and ratio columns slip in without error or warning — results just quietly distort. The more columns you have, the harder it is to spot by eye.

An ROI inflated 93× is actually more deceptive. It reads as "outstanding ad performance." If downstream consumers read the weekly report directly to make budget decisions, the wrong number propagates all the way into operational actions. In this incident, several read-only consumers queried the weekly table directly — until the table was corrected, every consumer was reading wrong data.

The Fix​

Step 1: Keep only additive columns in the aggregation​

CREATE VIEW weekly_ad_totals AS
SELECT
  campaign_id,
  date_trunc('week', day) AS week,
  SUM(impressions) AS impressions,
  SUM(clicks)      AS clicks,
  SUM(cost)        AS cost,
  SUM(gmv)         AS gmv
FROM daily_ad_report
GROUP BY campaign_id, date_trunc('week', day);

Additive columns are counts/totals (impressions, clicks, cost, orders) — summing them across time intervals still means something.

Step 2: Recompute all ratios from totals after aggregating​

SELECT
  campaign_id,
  week,
  impressions,
  clicks,
  cost,
  CASE WHEN clicks > 0
       THEN cost / NULLIF(clicks, 0)::numeric
       ELSE 0 END AS ppc,
  CASE WHEN impressions > 0
       THEN clicks::numeric / NULLIF(impressions, 0)
       ELSE 0 END AS ctr,
  CASE WHEN cost > 0
       THEN (gmv - cost)::numeric / NULLIF(cost, 0)
       ELSE 0 END AS roi
FROM weekly_ad_totals;

Two details: PostgreSQL integer division truncates, so cast with ::numeric before dividing; return 0 when the denominator is 0, keeping the same convention as the daily layer.

Step 3: When you only have ratios, use a weighted average​

-- Aggregate daily CTR weighted by impressions (expanded form: SUM(ctr × impressions) / SUM(impressions))
SELECT
  date_trunc('week', day) AS week,
  SUM(clicks)::numeric / NULLIF(SUM(impressions), 0) AS ctr_weighted
FROM daily_ad_report
GROUP BY date_trunc('week', day);

A weighted average is essentially "reconstruct numerator and denominator, then divide" — as long as you can still access the weight column, always prefer it over a plain average.

After the fix, all 13 ratio columns in the weekly report matched the measured daily values. Historical dirty data was backfilled with the same formulas, and read-only downstream consumers became correct automatically — without changing a single line of code.

FAQ​

Can you sum percentages?​

No. Percentages and ratios are relative values with different denominators per row — SUMming them yields a sum of N relative values, inflated by the number of periods aggregated, with no business meaning. The correct approach is to skip ratio columns during aggregation and recompute from totals afterward (clicks ÷ impressions, cost ÷ clicks); when you only have ratios without totals, take a weighted average using the denominator as weight.

Can you add percentages together to get an average?​

Only when the denominators are identical. Averaging percentages with different denominators is an unweighted average that skews toward small-denominator items (extreme ratios from low-traffic days get amplified). The correct approach is to sum numerators and denominators separately, then divide — mathematically equivalent to a weighted average by denominator.

While triaging a dashboard lead in an ad-data pipeline: an offer was flagged "suspected paused" in the diagnostic detail, complete with a reinvestment suggestion — yet the "suggested actions" list had no row for it. The detail page pointed at a table that didn't contain it.

Encountered this while building AI Analytics — an LLM-powered analytics platform that surfaces market trends, user behavior, and sales data; the diagnostic detail and the suggestion list come from two adjacent queries in the pipeline.

TL;DR​

Two queries defined the same business predicate ("suspected paused") at different aggregation granularity: the detail query judged at campaign×offer level — any single campaign with no spend in the last week flags it; the candidate list judged at whole-offer level — all campaigns must be spend-free to qualify. One offer still had spend (3.09) in one campaign, so the detail flagged it while the summary skipped it, leaving a dangling reference in the UI. Three fixes: unify the definition at the coarser granularity, make the predicate CTEs verbatim-identical, and verify row-set containment before cross-referencing.

Symptoms​

Two queries, each with its own definition of "suspected paused":

-- Query 4, diagnostic detail: pair granularity (campaign × offer)
WITH consumption_paused_q4 AS (
  SELECT offer_id, campaign_id
  FROM spend_weekly
  GROUP BY offer_id, campaign_id        -- each campaign judged alone
  HAVING SUM(spend) FILTER (WHERE is_last_week) = 0
)

-- Query 5, suggestion candidates: offer granularity (summed across campaigns)
WITH suspected_paused_q5 AS (
  SELECT offer_id
  FROM spend_weekly
  GROUP BY offer_id                     -- the whole offer judged together
  HAVING SUM(spend) FILTER (WHERE is_last_week) = 0
)

The problematic offer hung under multiple campaigns, one of which still had spend (3.09) in its last week:

Query 4 (pair level):   campaign A last-week spend = 0    → flagged ✓, suggestion attached
Query 5 (offer level):  summed last-week spend = 3.09     → excluded ✗
UI: detail says "see the suggestion table"; the suggestion table has no such row

No errors, plausible totals — the dangling reference only surfaces when someone follows a specific detail row.

Root Cause​

The "same source" contract had a coverage gap. The pipeline spec requires the feature-column CTEs of adjacent queries to be verbatim identical — that rule was followed to the letter. But it only covers feature columns; the predicate set (the business judgment before WHERE) was out of scope. "Suspected paused" was implemented twice, at different granularities: pair-level judgment is sensitive to "one campaign has no spend", offer-level judgment to "all campaigns have no spend". For any offer spanning both situations, the two queries must disagree — the overlap zone is mathematically guaranteed.

Cross-query referencing amplified the fork into a dangling reference: the UI treats query 4's rows as details and query 5's table as the entry point, with nobody ever checking "rows(q4) ⊆ rows(q5)". Aggregation-level inconsistency is the classic data-warehouse consistency trap — our earlier post on SUM over ratio columns inflating aggregated metrics is the same disease in another organ: aggregation happening at the wrong level.

Solution​

Step 1: Decide the canonical definition before touching SQL​

The business question has one answer: "should we keep funding this offer" is an offer-level decision, so the predicate must tighten to whole-offer granularity — every attached campaign spend-free in the last week AND no operation annotation rows. Bump the rule version so the change is traceable.

Step 2: Share the predicate CTE verbatim​

Extract the predicate CTE into one text block referenced by both queries; upgrade the contract at the same time: verbatim sharing covers the predicate set (including granularity), not just feature columns. One definition, one place to change.

Step 3: Before cross-referencing, check row-set containment​

Make "marker set ⊆ target row set" a standing check (promote it to a test):

-- Dangling detection: flagged by q4 but absent from q5
SELECT q4.offer_id
FROM consumption_paused_q4 q4
LEFT JOIN suspected_paused_q5 q5 USING (offer_id)
WHERE q5.offer_id IS NULL;

Only when this returns 0 rows does the UI earn the right to render both outputs on one page.

Step 4: Replay against history before shipping​

Replay the new definition over historical snapshots: verify every verdict flip (flagged ↔ normal) is correct, with zero collateral flips and zero dangling references, then re-run production validation.

FAQ​

Why do two SQL queries return different results on the same data?​

Three usual differences: aggregation granularity (different GROUP BY dimensions — campaign×entity vs whole entity in this case), filter logic (different WHERE/HAVING conditions), and point in time. Granularity is the sneakiest: both queries are individually correct, yet their verdicts can contradict.

When do I use HAVING vs WHERE for aggregate conditions in SQL?​

WHERE filters rows before grouping; HAVING filters groups after. But before picking the keyword, pick the granularity — "what counts as one group" determines sensitivity: finer granularity flags more easily (any group qualifies), coarser granularity is conservative (all groups must qualify). Different granularity, opposite verdicts.

How do I keep data definitions consistent across reports?​

One definition per business predicate, predicate CTEs shared verbatim across queries, containment checks (A ⊆ B) before cross-query references, and versioned definition changes replayed over history. Consistency doesn't come from convention — it comes from contracts plus checks.

After switching an ads data source from daily tables to weekly tables, I found an empty table still sitting in the schema — one that was only ever CREATEd in the earliest migration, held zero rows, and was never referenced by runtime code. It even carried stale field names and unnormalized columns.

Encountered this while building AI Ops — AI-powered analytics that surfaces market trends, user behavior, and sales data to drive precise operational strategy. After this data-source switch, the write path had long since moved to the new weekly tables and a cleanup migration had already dropped the old daily table. The one thing missed was a monthly table that existed only in the baseline CREATE — it had no new writes, no corresponding DROP, and just sat dormant in the schema with its deprecated field definitions.

TL;DR​

The signature of an orphan table: only CREATEd in an early/baseline migration, zero references in current code, often with stale or unnormalized column names. Batch migrations don't touch these automatically. You have to actively list every table in the schema with information_schema.tables, compare against code references, identify the orphans, and write a DROP migration to clean them up — not just psql your way to a one-off delete.

The symptom​

A typical orphan table looks like this:

• Zero rows — the business stopped writing to it long ago;
• Zero runtime references — no SELECT/INSERT anywhere in code, only the CREATE in a migration file;
• Stale fields — column names from a previous naming convention (e.g. ad_plan_id/product_id), out of step with current standards;
• Unnormalized columns — possibly even Chinese column names that were never cleaned up.

It doesn't crash and doesn't affect production, so from a "nothing's broken online" perspective it's invisible. But the harm is implicit: it misleads newcomers into thinking it's still in use, pollutes the schema namespace, adds noise to cross-table audits, and could be read as dirty data by some mistaken SELECT *.

Root cause​

Database migrations follow a pervasive pattern: migrations are "additive".

A data-source switch typically evolves like this:

1. An early baseline migration CREATEs a batch of tables (daily, monthly);
2. Once the business runs, the write path starts depending on them;
3. Requirements change, new tables (weekly) are introduced, and writes migrate over;
4. The old daily table's writes stop, and a migration DROPs it;
5. But the monthly table (or any table that was only ever CREATEd in the baseline and never directly used by the write path) gets no corresponding DROP.

The problem is step 5: migration attention focuses on "tables in use right now" — which ones are being written, which ones queries hit. A table that "once existed but never entered the main path" is in neither the write path nor the query path, so it never triggers a DROP and becomes an orphan. This is the same family as Airflow DAG metadata lingering after deletion: "removed the entry, forgot to clean the structure" — a high-frequency failure mode in migration work.

The fix​

Core flow: list all tables → compare references → confirm empty → write a DROP migration → verify.

Step 1: list all base tables in the schema with information_schema​

-- List all base tables in a schema (exclude views)
SELECT table_name
FROM information_schema.tables
WHERE table_schema = 'your_schema'
  AND table_type = 'BASE TABLE'
ORDER BY table_name;

information_schema.tables is a SQL-standard catalog view, portable across PostgreSQL/MySQL/SQL Server with stable fields — ideal for baking into an audit script.

Step 2: grep the codebase to confirm runtime references​

For each candidate table, search for references in the codebase, excluding migration files themselves:

# Search runtime code references, excluding the migrations directory
grep -rn "ad_product_monthly_stats" src/ --include="*.py" \
  | grep -v "migrations/"
# 0 lines of output → no runtime reference, it's a candidate

Zero references is the key evidence for an orphan. Make sure to exclude the migration directory — the CREATE in the baseline doesn't count as a "reference".

Step 3: confirm it's empty​

SELECT count(*) FROM your_schema.ad_product_monthly_stats;
-- 0 → confirmed no data, safe to clean up

Be extra careful with tables that have data: first confirm they're truly abandoned (not just recently unwritten), and back up logically if in doubt.

Step 4: write a DROP migration (not a manual delete)​

-- db-migrations/{project}/027_drop_ad_product_monthly_stats.sql
DROP TABLE IF EXISTS your_schema.ad_product_monthly_stats;

Always go through a migration file: it's version-controlled, replayed consistently across environments (dev/staging/prod), and leaves an audit trail. A one-off psql delete only works on the current machine — on another box, the table grows back.

Step 5: verify the drop​

SELECT to_regclass('your_schema.ad_product_monthly_stats');
-- Returns NULL → the table no longer exists

to_regclass() is the standard way to check whether a relation exists; NULL confirms the drop succeeded.

Batch audit: sweep same-prefix siblings at once​

After dropping one table, list all same-prefix siblings and walk through each — avoid "dropped one, missed its siblings":

-- List all tables under a prefix, run steps 2-5 on each
SELECT table_name
FROM information_schema.tables
WHERE table_schema = 'your_schema'
  AND table_name LIKE 'ad_%'
ORDER BY table_name;

FAQ​

How do I list all tables in a PostgreSQL database?​

Query information_schema.tables, filtering by table_schema and table_type = 'BASE TABLE' to list all base tables in a schema. It's more scriptable than psql's \dt, and because it's the SQL standard, the same query is portable across databases.

How do I find unused or orphan tables in PostgreSQL?​

List all tables with information_schema.tables, then compare against references in your codebase or query logs. Tables with zero runtime references and no writes are orphan candidates; confirm the row count with SELECT count(*), and once you've verified no data and no foreign-key dependencies, write a DROP migration to clean them up.

What's the difference between information_schema and pg_catalog?​

information_schema is the SQL-standard catalog view — portable across PostgreSQL/MySQL/SQL Server with stable fields, ideal for portable audit scripts. pg_catalog is the PostgreSQL-specific catalog, richer and more detailed (e.g. precise row-count estimates, storage details) but subject to change between versions. For portable schema audits, prefer information_schema.

When you persist a dict containing a Python set with json.dumps(data, default=str) and later read it back to test membership with in, the result is silently wrong — no exception, but the in checks are completely off.

Encountered this while building AI Ops — AI-powered analytics that surfaces market trends, user behavior, and sales data to drive precise operational strategy. In a decision-replay feature for an analysis template, I needed to serialize a "missing months" set into a snapshot and read it back to decide whether a given month was missing. After replay, months that should have been flagged "missing" were silently judged "not missing" — with zero exceptions anywhere in the chain.

TL;DR​

default=str is not a universal escape hatch. It hands set to str(), storing a literal string like "{1, 2}" in the JSON instead of an array; the type is irreversible on reload, and an in check against it degrades to substring matching, silently returning wrong results. When set is involved, the correct approach is to convert to list before serializing and rebuild with set() on read.

The symptom​

This code fully reproduces the silent failure:

import json

# A dict containing a set — say, "months that still need backfill"
data = {"missing_months": {"3", "5", "12"}}

# Serialize with default=str (the common "just don't crash" shortcut)
serialized = json.dumps(data, default=str)
print(serialized)
# {"missing_months": "{'3', '5', '12'}"}   ← a string, not an array!

# Read it back
back = json.loads(serialized)
value = back["missing_months"]
print(type(value))   # <class 'str'>   ← no longer a set

# Silent bug: you meant to test membership in the "missing" set
print("1" in value)   # True   ← 1 is NOT in {3,5,12}, but "1" is a substring of "12"!
print("3" in value)   # True   ← correct by coincidence
print("9" in value)   # False

"1" in value returns True, yet the original set {"3", "5", "12"} does not contain "1". No exception, no warning — the result is just quietly wrong. This kind of bug is especially dangerous in branches that act on the check (e.g. "is this month missing data? if so, backfill it").

Root cause​

Three layers:

Layer 1: set is not JSON serializable to begin with. JSON has only array (list) and object — no set type. A direct json.dumps({"x": {1, 2}}) raises TypeError: Object of type set is not JSON serializable.

Layer 2: default=str turns the error into silent corruption. The default parameter of json.dumps is called for objects that can't be serialized, and is expected to return a serializable value. When default=str, the object goes to str() — so a set becomes its Python literal form {'3', '5', '12'}, stored as a string in the JSON:

>>> json.dumps({"m": {"3", "5", "12"}}, default=str)
'{"m": "{\'3\', \'5\', \'12\'}"}'

The error is gone — at the cost of the type silently changing from set to str, with no signal that it happened.

Layer 3: in means different things for str vs set. This is the core of the silent bug. For set/list, x in s is a membership test; for str, x in s degrades to substring matching. The reloaded value is the string "{'3', '5', '12'}", so "1" in "{'3', '5', '12'}" tests whether the substring "1" appears — and since "12" contains "1", it returns True.

This is the same family of trap as Airflow PostgresHook silently dropping multi-statement SQL results: the most dangerous bugs don't throw — they silently return the wrong answer, leaving you no signal to investigate.

The fix​

Core principle: store only standard JSON types; rebuild set semantics on the read side.

Option 1: explicitly convert to list before serializing (recommended)​

The most direct and controllable approach — when you know where the set is, convert it to list in place:

import json

# Before serializing: set → list (a standard JSON array)
data = {"missing_months": list({"3", "5", "12"})}
serialized = json.dumps(data)
print(serialized)
# {"missing_months": ["3", "5", "12"]}   ← a proper JSON array

# Rebuild the set after reading back
back = json.loads(serialized)
months = set(back["missing_months"])
print("1" in months)   # False ✓
print("3" in months)   # True  ✓

The serialized result is a clean JSON array — portable, readable, and restorable.

Option 2: a custom default function (when data is complex)​

If the data structure is deep and you're not sure where a set might sneak in, use a default function dedicated to collection types — preserving semantics while still falling back for other non-standard types:

import json

def safe_default(obj):
    # Collection types → list, kept as a standard JSON array
    if isinstance(obj, (set, frozenset)):
        return sorted(obj)          # sort for stable, predictable output
    # Only fall back to str for types that truly can't be represented
    return str(obj)

data = {"missing_months": {"3", "5", "12"}, "created_at": some_datetime}
serialized = json.dumps(data, default=safe_default)
# {"missing_months": ["3", "5", "12"], "created_at": "..."}

back = json.loads(serialized)
months = set(back["missing_months"])
print("1" in months)   # False ✓

Compared to a blind default=str, this function handles "types you need to preserve" (collections) explicitly and only falls back to str for genuinely unrepresentable types — minimizing silent risk.

FAQ​

How do I convert a Python set to JSON?​

A set has no native JSON type, so json.dumps raises TypeError. Convert it with list(set) before serializing to store a standard JSON array, then rebuild with set() when reading it back. This avoids the error and fully restores the set semantics, across languages too.

How do I fix "Object of type set is not JSON serializable" in json.dumps?​

The root cause is that sets aren't JSON serializable. The safe fix is to convert the set to a list before dumping, or pass a default function that returns list(obj) for isinstance(obj, (set, frozenset)). Avoid default=str — it doesn't crash, but it stores the set as a string, so the type can't be restored on read.

Why does in return wrong results after serializing a set with default=str?​

default=str passes the set to str(), storing the literal string '{1, 2}' in JSON. On reload the value is a str, not a set, so x in s degrades from membership testing to substring matching — e.g. "1" in "{'3','5','12'}" returns True because "12" contains the character "1", even though the original set doesn't contain "1". The fix is to serialize as list and rebuild with set() on read.