Your trace UI can hide a secret that SQLite still stores

I made a tiny trace store hide a fake token in its display. Then I opened the database through a fresh connection. The token was still there. So was a made-up confidential sentence.

The redaction worked exactly where I put it. That was the problem. 🫠

A current DEV discussion about secrets in coding-agent context has a useful thread on canaries: harmless markers placed in a nonproduction workflow to check where data travels. The comments pushed beyond matching token prefixes to checking plain prose and persisted copies. [1]

I turned that distinction into a small SQLite regression example. No real credentials, external service or model call is involved.

Original diagram of the executed local example. It does not depict a tested third-party agent or logging product.

Put the test below the display

The synthetic event contains an operation, a status, a fake token and a prose note. The first capture path stores the whole event, then replaces the token when displaying it. That view still exposes the prose, and its raw database row retains both markers.

The second path builds a new object containing only approved operation and status values before writing anything. Unknown fields disappear because they never enter that object. The allowed fields also have constrained values, so arbitrary text cannot sneak through by calling itself an operation. 🧱

OWASP’s logging guidance calls out access tokens, passwords, connection strings and higher-classification data as things that generally should not be recorded directly. It also includes logging behavior in application verification. [2] The practical question here is whether those rules apply before persistence or only when someone reads a pretty trace page.

Run the counterexample

Save this as trace_canary.py and run python3 trace_canary.py. It uses only Python’s standard library. Temporary files are removed when the program exits.

import json
import sqlite3
import tempfile
from pathlib import Path
token = "demo-token-000"  # Synthetic: never a working credential.
prose = "The violet turnip project is still confidential."
event = {"operation": "search", "status": "ok", "token": token, "note": prose}
def display(row):
    return row.replace(token, "[redacted]")
def metadata(row):
    if row["operation"] not in {"search", "parse"} or row["status"] not in {"ok", "error"}:
        raise ValueError("Unexpected metadata value")
    return {"operation": row["operation"], "status": row["status"]}
with tempfile.TemporaryDirectory() as directory:
    path = Path(directory) / "traces.sqlite"
    with sqlite3.connect(path) as db:
        db.execute("CREATE TABLE traces (mode TEXT, payload TEXT)")
        db.executemany("INSERT INTO traces VALUES (?, ?)", [
            ("raw", json.dumps(event)), ("projected", json.dumps(metadata(event)))
        ])
    db.close()
    with sqlite3.connect(path) as db:  # Fresh connection, no display function.
        rows = dict(db.execute("SELECT mode, payload FROM traces"))
    db.close()
    exports = {}
    for mode, payload in rows.items():
        export = Path(directory) / f"{mode}.json"
        export.write_text(payload, encoding="utf-8")
        exports[mode] = export.read_text(encoding="utf-8")
    assert token not in display(rows["raw"]) and prose in display(rows["raw"])
    assert token in rows["raw"] and prose in rows["raw"]
    assert token in exports["raw"] and prose in exports["raw"]
    assert all(value not in rows["projected"] for value in (token, prose))
    assert all(value not in exports["projected"] for value in (token, prose))
    print("Token hidden in display; token and prose remain in raw storage/export")
    print("Projected storage contains neither canary")
    print(rows["projected"])

Output:

Token hidden in display; token and prose remain in raw storage/export
Projected storage contains neither canary
{"operation": "search", "status": "ok"}

The fresh connection matters: it reads the persisted payload without calling display. Each export is also written and read back independently. The assertions require the deliberately unsafe control to retain both markers and the projected path to retain neither. ✅

I separately checked that the cleanliness assertion actually fails on raw capture, passes on the projected object, and that an arbitrary operation value is rejected. A test that passes the broken path would only teach the logger how to look convincing.

Keep the diagnostic value, choose the data deliberately

This example keeps enough metadata to say which kind of operation completed and whether it succeeded. A real service might also need a bounded duration, an approved error code or a request identifier. Decide those fields explicitly and consider whether each identifier itself exposes sensitive information.

⚠️ A field-name allowlist is insufficient if an allowed message or error field still accepts arbitrary text. Here the value checks are intentionally narrow. Free-text diagnostics need a separate design, access policy and retention decision.

The exact canary match proves only what happened to those markers on these paths. Encoding, splitting, compression or paraphrasing could hide them from this assertion. Copies in backups, queues, analytics and vendor traces also need separate inspection. This is a regression pattern for a controlled local component, not evidence that any named coding assistant leaks data or that one function secures an agent.

💡 For an existing pipeline, place a fake token and fake prose in a throwaway input, trigger the real capture path, then inspect the store and the actual export under appropriate access. Keep that test isolated from real secrets. A clean UI answers a display question; the stored bytes answer a different one.

References and visual credit

[1] Dimitris Kyrkos’s DEV discussion and comments on canary tests and persisted traces.

[2] OWASP Logging Cheat Sheet: data to exclude and verification.

[3] Python sqlite3: transactions and connection context managers.

Original diagram and synthetic example. All canaries are invented and have no authentication capability.

AI disclosure

AI was used to draft the article.