6 posts tagged with "DevOps"

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 /.

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.

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.

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.

After deleting a DAG's .py file in Airflow to retire it, the dag_id still hangs around in the Web UI list and the database; even stranger — if you clear the metadata first and delete the file second, the just-cleared rows "come back to life."

Encountered this while building AI Ops — an LLM-powered analytics pipeline where retiring an old report DAG required cleaning its metadata too, otherwise the UI list and scheduled scans stayed polluted by residual rows.

TL;DR​

Airflow's dag-processor periodically scans the DAG folder and re-registers DAGs, and airflow dags reserialize doesn't purge "file-already-deleted" orphan rows — so just deleting the .py file won't make the dag_id vanish from the UI or DB. Conversely, clearing metadata before deleting the file lets the processor re-register the cleared rows on its next scan ("revival"). Correct order: ①delete the file first so the processor stops registering → ②SQL DELETE in foreign-key order → ③run airflow dags reserialize to verify.

Symptoms​

Retiring the shop_report_aggregation DAG — after deleting its .py file:

$ ls /opt/airflow/project/airflow_dags/shop_report_aggregation.py
ls: cannot access '.../shop_report_aggregation.py': No such file or directory

$ # but it's still in the database
$ docker exec cclhub-db psql -U airflow -d airflow -c \
    "SELECT dag_id, is_paused, is_active FROM dag WHERE dag_id='shop_report_aggregation';"
       dag_id            | is_paused | is_active
--------------------------+-----------+-----------
 shop_report_aggregation | f         | t          ← still there

It's not just the dag table — the matching rows in serialized_dag, dag_code, and dag_version are all still there, so the Web UI keeps showing this "deleted" DAG.

Worse is the reverse order — clear metadata first, delete file second:

T0  DELETE FROM dag WHERE dag_id='shop_report_aggregation';   ← cleared
T1  (.py file not deleted yet)
T2  dag-processor scan fires; file exists, dag table has no row → re-registers
T3  SELECT ... FROM dag WHERE dag_id='shop_report_aggregation';   ← it's back (revival)

Root Cause​

Two mechanisms stack up:

1. dag-processor scans and re-registers periodically. Airflow's dag-processor (part of the Scheduler) scans dags_folder on processor_poll_interval (default ~5 min), parses each .py file, and upserts into the metadata tables (dag, serialized_dag, dag_version). As long as the file exists, the next scan rewrites those rows. That's the direct source of "revival" — you clear the row, the file is still there, and the processor re-registers it as a new DAG.

2. reserialize ignores "file-gone" orphan rows. airflow dags reserialize re-serializes existing DAG files and refreshes serialized_dag; it does not delete orphan dag rows whose files have vanished. And airflow dags cleanup only purges expired dag_run history by default — it also leaves the dag / serialized_dag / dag_code / dag_version metadata tables alone. So after you delete the file, the metadata rows become orphans nobody cleans.

┌─ dag-processor ──────────────────────────────┐
│  scans dags_folder                            │
│  ├─ file present → upsert dag / serialized... │  ← source of revival
│  └─ file absent  → skip, no row deletion      │  ← orphan residue
└───────────────────────────────────────────────┘

Conclusion: to actually remove the metadata, you must give the processor no file to register (delete the file first), then manually clean the residual rows.

Solution​

Step 1: Delete the file first​

Make the .py file disappear from the DAG folder so dag-processor stops registering it.

# In production this is usually synced to the volume-mounted DAG folder
# /opt/airflow/project/airflow_dags/ via git pull
git pull   # removes shop_report_aggregation.py from the repo and the folder

# or delete directly (after confirming nothing depends on it)
rm /opt/airflow/project/airflow_dags/shop_report_aggregation.py

Step 2: Clean metadata in foreign-key order​

DELETE in foreign-key dependency order to avoid constraint violations. Deleting dag_run CASCADEs to task_instance:

BEGIN;

-- 1. run history (CASCADEs to task_instance)
DELETE FROM dag_run      WHERE dag_id = 'shop_report_aggregation';

-- 2. serialized DAG
DELETE FROM serialized_dag WHERE dag_id = 'shop_report_aggregation';

-- 3. version
DELETE FROM dag_version  WHERE dag_id = 'shop_report_aggregation';

-- 4. dag main table
DELETE FROM dag          WHERE dag_id = 'shop_report_aggregation';

-- 5. dag_code is keyed by source hash; multiple DAGs may share the same code;
--    only delete hashes no longer referenced by any serialized_dag
DELETE FROM dag_code
WHERE dag_hash NOT IN (SELECT dag_hash FROM serialized_dag);

COMMIT;

Step 3: Verify​

airflow dags reserialize

# confirm the dag row is not rebuilt
docker exec cclhub-db psql -U airflow -d airflow -c \
  "SELECT count(*) FROM dag WHERE dag_id='shop_report_aggregation';"
#  count
# -------
#      0   ✅

After reserialize, dag / serialized_dag / dag_code / dag_version are all 0 for that dag_id, and the next processor scan doesn't rebuild them — the cleanup is stable.

As a side note, on the same pipeline, pandas NaN crashing XCom serialization is another pitfall worth bookmarking.

Notes​

FAQ​

Why does a DAG still show in Airflow after deleting its .py file?​

Deleting the file doesn't clean the database. Rows in dag / serialized_dag / dag_code / dag_version still exist, and the Web UI reads those tables to render the list, so the deleted DAG keeps showing. Airflow has no built-in command to purge these orphan rows automatically; you must SQL DELETE them manually in foreign-key order.

How do I completely delete an Airflow DAG and all its metadata?​

Three steps: 1) delete the .py file so dag-processor stops registering it; 2) SQL DELETE in foreign-key order (dag_run → serialized_dag → dag_version → dag → orphan dag_code); 3) run airflow dags reserialize, then query the dag table to confirm the dag_id row count stays at 0 and isn't rebuilt.

What's the correct order to clean Airflow DAG metadata, and why not clear metadata before deleting the file?​

Delete the file first, then clean metadata. If you reverse it, the .py file still exists, so dag-processor re-registers the cleared dag row on its next scan — the metadata "comes back to life." Only by making the file vanish first (so the processor has nothing to register) and then cleaning the residual rows can you fully retire the DAG.

Encountered this while building AI Ops — LLM-powered analytics that surfaces market trends, user behavior, and sales data for precise operational strategy.

TL;DR​

dotenv treats # in unquoted values as an inline comment. KEY=value#hash is actually loaded as value, with #hash dropped — no warning, no error. Fix: wrap any .env value containing #, spaces, or special characters in double quotes — KEY="value#hash".

Symptom​

Backend calls to an upstream service keep returning 401 Invalid credentials:

POST /api/v1/dag/trigger → 500
Stack: Airflow JWT auth failed (401): {"detail":"Invalid credentials"}
  at getJwtToken (airflow-client.ts)

Investigation shows the password written in .env is 24 chars and contains # and &:

AIRFLOW_PASSWORD=ooGR0^kThVI&ag#RyCpUmbIr

But the value loaded into process.env.AIRFLOW_PASSWORD is only 10 chars long — #RyCpUmbIr is gone. Calling the upstream auth endpoint with the full password from CLAUDE.md returns 201; calling it with the truncated value from .env returns 401. The credentials are fine; the value loaded from .env is truncated.

Root Cause​

dotenv follows shell convention: anything after # in an unquoted value is treated as an inline comment.

# .env
AIRFLOW_PASSWORD=ooGR0^kThVI&ag#RyCpUmbIr
# dotenv actually parses:
#   AIRFLOW_PASSWORD = "ooGR0^kThVI&ag"
#   #RyCpUmbIr       ← dropped

This behavior is documented, but there is no warning or log. What the runtime gets is a silently truncated string. Combined with shell-escaping semantics for &, spaces, and $, the bug is even more hidden:

Strong-random strings like JWT_SECRET, API_KEY, and DATABASE_URL frequently contain # — high-risk territory.

Solution​

Wrap any value with special characters in double quotes in .env:

# .env
AIRFLOW_PASSWORD="ooGR0^kThVI&ag#RyCpUmbIr"
JWT_SECRET="abc#def$ghi jkl"
DATABASE_URL="postgres://user:p@ss#word@host:5432/db"

Restart the service to apply:

# pm2
pm2 restart analytics-api --update-env

# docker compose
docker compose restart api

# systemd
sudo systemctl restart api

Why it works: when dotenv sees double quotes, it reads the value literally up to the closing quote. #, spaces, and $ are not special-cased (unless you explicitly enable expand). Verify the loaded value immediately after the fix:

// Validate critical env vars at startup to catch truncation early
const required = ['AIRFLOW_PASSWORD', 'JWT_SECRET', 'DATABASE_URL'] as const;
for (const key of required) {
  const v = process.env[key];
  if (!v || v.length < 16) {
    throw new Error(`${key} not loaded correctly (length ${v?.length ?? 0}); check .env quoting`);
  }
}

This turns dotenv's silent failure into a startup failure, exposing the bug immediately the next time.

FAQ​

Why does a password with # in .env get shorter?​

dotenv treats everything after # as an inline comment by default and drops it. Unquoted KEY=value#hash is loaded as just value, with no error or log. Wrap the value in double quotes — KEY="value#hash" — to preserve the full content.

How do I debug dotenv not working?​

Three steps: first confirm dotenv.config() runs before all imports (ES Module imports are hoisted statically — see debugging silent JWT signature failures); then verify .env values have no unescaped # or spaces; finally print process.env.XXX length and characters at startup and diff them char-by-char against the .env source file.