Security Findings Management and Remediation Tracking Questions

Direct answer

Read the CSV with csv.DictReader, keep rows whose status is open-like, parse due_date as ISO 8601 (no guessing of other formats), and compare it to today's date in one declared "policy" time zone. Rows whose date cannot be parsed are never silently dropped or guessed at: they go to a separate "bad dates" list that is printed for a human to fix.

Terms. ISO 8601 is the standard date format year-month-day (2026-09-28), optionally followed by a time and an offset (T23:30:00-07:00, meaning 7 hours behind UTC, the world reference time). A datetime with an offset is called aware (it says exactly which instant it is); one with no zone information, including a bare date, is called naive (it could be any zone). A policy zone is the single zone the organisation declares as the meaning of "due", and America/New_York is an IANA zone name (the standard tz database name that also knows daylight-saving rules). A quarantined row is one set aside in a separate list for a human to fix instead of being processed.

Time-zone rules

• Pick one policy zone for what "due" means (here America/New_York) and write it in the script. A date-only value (2026-09-28) means that calendar day in the policy zone.
• A value with an offset (2026-09-27T23:30:00-07:00) is converted to the policy zone first, and only then reduced to a date. Taking the date before converting gives the wrong day: that timestamp is 02:30 on 2026-09-28 in New York, so it is not overdue on 09-28, while a naive reading of the raw text (09-27) would call it 1 day overdue.
• "Today" must come from the same zone (datetime.now(POLICY_TZ).date()), otherwise items flip to overdue hours early or late.
• Overdue means due date strictly before today; an item due today is not yet overdue.

Bad-date rules

• Empty, impossible (2026-02-30) and ambiguous (09/12/2026 could be 9 December or 12 September) values are rejected, not guessed. Guessing produces confident wrong numbers.
• Every rejection is reported with its line number, ticket id and reason, so the count of overdue items is never quietly understated.

Script (Python 3.11 or newer, because earlier versions of datetime.fromisoformat accept only a narrow set of ISO forms; standard library only; today is pinned so the output is reproducible). Note that 3.11+ also accepts a space in place of the T (ticket 8, 2026-09-01 08:00:00), which is still ISO-like, so "strict" here means slash formats such as 09/12/2026 are refused, not that only one spelling passes.

import csv
import io
from datetime import date, datetime
from zoneinfo import ZoneInfo

POLICY_TZ = ZoneInfo("America/New_York")   # the org's declared "due date" timezone
OPEN_STATUSES = {"open", "in progress", "blocked"}

CSV_TEXT = """id,summary,owner,due_date,status
1,Patch OpenSSL,ana,2026-09-20,Open
2,Rotate keys,raj,2026-09-27T23:30:00-07:00,open
3,Fix XSS,li,2026-09-28,open
4,Close S3 bucket,sam,2026-02-30,open
5,Old finding,ana,2026-08-01,Closed
6,Enable MFA,raj,09/12/2026,open
7,Update TLS,li,,In Progress
8,Harden SSH,sam,2026-09-01 08:00:00,blocked
"""


def parse_due(raw):
    """Return a date in POLICY_TZ, or raise ValueError. Refuses ambiguous formats."""
    raw = raw.strip()
    if not raw:
        raise ValueError("empty due_date")
    dt = datetime.fromisoformat(raw)          # ISO 8601 only: 2026-09-20 or 2026-09-20T10:00:00+02:00
    if dt.tzinfo is None:                     # date-only or naive: interpret in the policy zone
        return dt.date()
    return dt.astimezone(POLICY_TZ).date()    # aware: convert first, then take the calendar date


def overdue(rows, today):
    good, bad = [], []
    for line_no, row in enumerate(rows, start=2):          # line 1 is the header
        if row["status"].strip().lower() not in OPEN_STATUSES:
            continue
        try:
            due = parse_due(row["due_date"])
        except ValueError as exc:
            bad.append((line_no, row["id"], row["due_date"], str(exc)))
            continue
        if due < today:
            good.append((row["id"], row["owner"], due, (today - due).days))
    good.sort(key=lambda r: -r[3])
    return good, bad


if __name__ == "__main__":
    today = date(2026, 9, 28)   # pinned; in production: datetime.now(POLICY_TZ).date()
    good, bad = overdue(csv.DictReader(io.StringIO(CSV_TEXT)), today)
    for tid, owner, due, days in good:
        print(f"OVERDUE id={tid} owner={owner} due={due} days_overdue={days}")
    for line_no, tid, raw, why in bad:
        print(f"BAD_DATE line={line_no} id={tid} value={raw!r} reason={why}")

Output:

OVERDUE id=8 owner=sam due=2026-09-01 days_overdue=27
OVERDUE id=1 owner=ana due=2026-09-20 days_overdue=8
BAD_DATE line=5 id=4 value='2026-02-30' reason=day 30 must be in range 1..28 for month 2 in year 2026
BAD_DATE line=7 id=6 value='09/12/2026' reason=Invalid isoformat string: '09/12/2026'
BAD_DATE line=8 id=7 value='' reason=empty due_date

The reason text after reason= comes from the interpreter and varies by version: the wording shown for 2026-02-30 is from Python 3.14, while 3.11 to 3.13 print day is out of range for month. The rows flagged, their order and the day counts are identical on every version from 3.11.

Reading the result

• Ticket 1: 2026-09-28 minus 2026-09-20 is 8 days. Ticket 8: 09-01 to 09-28 is 27 days.
• Ticket 2 (offset timestamp) and ticket 3 (due today) are correctly NOT overdue. Ticket 5 is closed, so skipped.
• Three rows are quarantined, and the overdue list is sorted worst first.

Complexity and edge cases

• O(n log n) for the sort, O(n) memory; for a huge export stream rows and sort only the good list.
• Watch for a byte-order mark (an invisible marker some tools write at the start of a file, which would corrupt the first header name id) or different header names/case in real exports, statuses with odd spelling ("Re-opened"), and due dates in the far past that hint at data-entry errors. In production, exit non-zero or alert if the bad-date count exceeds a threshold, so a broken export cannot masquerade as "nothing is overdue".

Direct answer

Closure means the weakness is demonstrably gone, not that a ticket moved to Done. The story to tell is one where you gave every fix a named owner, agreed in advance what proof would count, collected that proof, and kept chasing on a schedule. If you have no direct experience, describe the same loop as what you would set up.

Story skeleton (illustrative; swap in your own details and numbers)

Situation. A penetration test (an authorised, simulated attack on a system to find weaknesses) of a customer portal produced 23 findings: 2 critical, 6 high, 15 medium or low. The report sat in a shared folder and, two weeks later, nothing had moved.

Ownership. I split the findings by the team that owns the code, using the service catalog (a registry of which team runs which system), and opened one ticket per finding assigned to a named engineer, with the due date set by severity (critical 7 days, high 30). I held a 30-minute walkthrough per team so each engineer understood the exploit, not only the title.

Evidence. For each critical and high finding I wrote the acceptance check (the written test that decides whether a fix counts) into the ticket: the exact request that had worked, and the response that would prove it fixed. A ticket could move to "Fixed" by the developer, but only I could set it to "Verified" after rerunning the test (a retest, meaning repeating the original attack steps against the fixed system) and attaching the result. The retest always comes before Verified, never after.

Follow-up. A weekly 15-minute review of overdue items, escalation to the engineering manager three days before a due date was missed, and a written risk acceptance (a signed decision by the business to live with a known risk, with an owner and an expiry date) for anything the business chose not to fix.

Result. Of the 8 critical and high findings, 7 passed my retest and were set to Verified by their deadlines. The eighth, a high, was marked "Fixed" by the developer, but my retest showed the same flaw still reproduced through a second endpoint. I did not set Verified. I sent it back with the new evidence, the team fixed both endpoints, and it passed a second retest a few days after its deadline, which I reported as one deadline miss. So the honest tally is 7 of 8 on time and 8 of 8 verified. Two mediums were formally accepted with expiry dates. I only caught the second endpoint because I reran the real request instead of accepting the developer's word.

If you lack direct experience

Say so plainly, then walk through the same four steps: assign an owning team per finding, define proof before work starts, verify independently, and chase on a fixed cadence with an escalation ladder. Naming what you would attach as evidence (the retest output, a configuration diff, a passing scan) shows you know what "verified" means.

A scripted version you can say aloud: "I haven't run this end to end, but here is how I would. First, I'd give every finding one named owner from the service catalog. Second, before any work starts, I'd write into the ticket what proof I will accept, for example the original request and the response that shows it is blocked. Third, the developer marks it Fixed, but I, or another person who did not make the change, rerun that request and attach the result before it can be Verified. Fourth, I'd review overdue items weekly and escalate to the manager before a deadline is missed. If I found the fix only worked on one URL, I'd send it back."

Pitfalls

• "The developer said it's fixed" is a status, not evidence.
• Verifying only the exact URL from the report misses the same flaw on sibling endpoints.
• A story with no checkable outcome sounds like activity, not closure.

Direct answer

I would build the platform around one central findings ledger (a database that holds every finding as a single record with a lifecycle), fed by connectors, deduplicated by a stable fingerprint, and connected outward by ticket adapters and a retest workflow. The three audiences get three views over the same data, so nobody keeps a private spreadsheet.

Core versus optional. The core is the connectors, the normalizer, the fingerprint dedup, the ledger and the ticket adapters. Retest orchestration and the evidence store are what make it audit-grade but can arrive in a second phase. Everything else (dashboards beyond three basic views, fuzzy-match tuning) is refinement.

Architecture

flowchart LR
    A[Scanner and pentest connectors] --> B[Normalizer]
    B --> C[Fingerprint and dedup]
    C --> D[(Findings ledger)]
    D --> E[Ticket adapters]
    E --> F[Team ticketing tools]
    D --> G[Retest orchestrator]
    G --> H[(Evidence store)]
    D --> I[Views: exec, security, developer]
    F -->|status webhooks| D

Components and the decision in each

• Connectors. (Core.) One per scanner or pentest format (scanner APIs, SARIF, a standard JSON format for static-analysis results, or CSV imports, report uploads). They only fetch; they do not interpret.

• Normalizer. (Core.) A translation step that maps every source to one schema: asset, vulnerability type or CVE (Common Vulnerabilities and Exposures ID), location (URL and parameter, or package and version), severity, scanner, timestamps, raw evidence pointer.

• Fingerprint and dedup. (Core.) The fingerprint is a hash (a fixed-length code computed from text, identical for identical input) of (asset, vulnerability identity, location). Same fingerprint from any scanner or run is an occurrence (one sighting of the finding by one scanner in one run) added to one finding, not a new finding. Fuzzy matches (same asset and CWE, the Common Weakness Enumeration catalogue of weakness types, but different wording) go to a review queue rather than merging silently. Re-detection of a closed finding reopens it.

  Worked example. Scanner A reports SQL injection (CWE-89) on shop.example.com at /search?q. Scanner B reports "SQL Injection (blind)" on the same host, same CWE, same location. Both hash shop.example.com|CWE-89|/search?q to the same fingerprint, so the ledger holds 1 finding with 2 occurrences and opens 1 ticket. A third report for the same host and CWE-89 at /login has a different location, so it is a new finding. A fourth report says CWE-89 on shop.example.com with no location at all: it cannot be matched exactly, so it lands in the review queue, where a person either links it to /search?q or /login or confirms it as new.

• Findings ledger. (Core.) Findings, occurrences, state history (append-only), owner, due date, acceptance records. State machine (a fixed list of allowed states and allowed moves between them, so a finding cannot jump from new to verified fixed): new, triaged, ticketed, fix submitted, verified fixed, risk accepted.

• Ticket adapters. (Core.) A single internal interface (create, update, comment, read status) with one adapter per tool (Jira and ServiceNow are common ticketing tools where developers track work). Each create carries an idempotency key: a unique label such as the finding's fingerprint, so if the request times out and is sent again with the same key, the ticketing side returns the ticket it already made instead of creating a second one. Routing uses service ownership data from the asset inventory.

• Retest orchestrator. (Phase two.) The coordinator that, when a ticket says fixed, schedules the original test (rescan or a pentester task) and only a passing result moves the finding to verified fixed.

• Evidence store. (Phase two.) Object storage (a service that stores whole files by name, such as S3) with write-once retention (files cannot be changed or deleted until a set date), files hashed at upload, access logged. Findings hold pointers, not blobs.

• Views. Executives: risk posture, aging against SLA (the service-level agreement, the fix-by deadline per severity), top risks. Security: triage queue, duplicate review, exceptions, connector health. Developers: their team's findings, exact reproduction and fix guidance, one-click retest request.

Cross-cutting

• Access control. Pentest detail can be sensitive: row-level access (each user sees only the records their team owns) by owning team; executives see aggregates.
• Scale. Ingest through a queue so a 200,000-row scan import does not block; dedup is a keyed lookup, roughly linear in incoming rows.
• Failure handling. Connector failures raise alerts and never silently produce empty reports (an empty result must be distinguishable from "no data").

Trade-offs

Buy versus build: commercial vulnerability-management suites (from vendors such as Tenable, Qualys or Rapid7) and open-source tools like DefectDojo cover ingest, dedup and dashboards; the parts organisations usually still build are ticket routing to their own tools and evidence rules. I would evaluate buying the core and building only the glue, unless the ticketing landscape is highly unusual. Merging too aggressively hides distinct issues on different hosts; merging too little floods developers. Start conservative and tune from the review queue.

Direct answer

Three separate queries over the same two tables, each with a precise definition written down first. (1) P90 (90th percentile, the value 90% of fixed findings beat) time to remediate, per severity, using a window function because SQLite and many warehouses have no built-in percentile. (2) Reopen rate per asset classification = findings that were reopened at least once divided by findings that were ever closed. (3) Mean time between recurrences = the average gap between successive new findings on the same asset. Definitions matter more than syntax here, because the schema only stores a reopen counter, not reopen dates.

Plain-language key

• A window function does a calculation across a set of rows without collapsing them into one row the way GROUP BY does. OVER (...) says which rows it looks at: PARTITION BY severity means "treat each severity as its own separate group", and ORDER BY days means "line the rows up shortest to longest inside each group".
• ROW_NUMBER() then numbers the rows 1, 2, 3... inside each group in that order, and COUNT(*) OVER (PARTITION BY severity) stamps every row with its group's size, n.
• A CTE (common table expression, the WITH name AS (...) part) is just a named temporary result so the next step can read from it.
• Nearest-rank percentile: sort the values, then take the one at position ceil(0.9 x n), where ceil rounds up to the next whole number. With n = 10 that is position 9; with n = 3, ceil(2.7) = 3.
• julianday(date) turns a date into a number of days, so subtracting two of them gives a gap in days. A comparison such as reopened_count > 0 yields 1 (true) or 0 (false) in SQLite, so SUM of it counts the true rows.
• A scanner rule is the specific check a scanning tool ran (for example "SQL injection in a query parameter"). A stable finding key is an identifier that stays the same each time the same weakness is reported, such as asset plus scanner rule. A rescan is running the scanner again to confirm a fix.

Definitions and assumptions (state these out loud)

• Time to remediate = fixed_at - discovered_at in days, for fixed findings only. Still-open findings are excluded (they would need a separate "open age" metric, otherwise the P90 is biased low).
• For a reopened finding, fixed_at is assumed to be the latest fix.
• Reopen rate denominator = findings closed at least once (fixed_at IS NOT NULL OR reopened_count > 0), so open findings never dilute it.
• Recurrence = a new finding row on the same asset (any new row counts, even for a different weakness). A true "same weakness came back" measure would need a stable finding key (asset + scanner rule); the given columns only allow the per-asset version.

Sample data (14 findings, 3 assets, run in SQLite 3.51)

CREATE TABLE assets (id INTEGER PRIMARY KEY, owner TEXT, classification TEXT);
CREATE TABLE vulnerabilities (
  id INTEGER PRIMARY KEY, asset_id INTEGER, severity TEXT,
  discovered_at TEXT, fixed_at TEXT, reopened_count INTEGER, scanner_name TEXT);

INSERT INTO assets VALUES (1,'payments','restricted'),(2,'web','internal'),(3,'hr','restricted');

INSERT INTO vulnerabilities VALUES
 (1, 1,'high','2026-01-01','2026-01-03',0,'scanA'),
 (2, 1,'high','2026-01-05','2026-01-10',1,'scanA'),
 (3, 2,'high','2026-01-08','2026-01-14',0,'scanA'),
 (4, 2,'high','2026-01-10','2026-01-20',0,'scanB'),
 (5, 3,'high','2026-01-12','2026-01-24',0,'scanA'),
 (6, 3,'high','2026-01-15','2026-02-04',2,'scanB'),
 (7, 2,'high','2026-02-01','2026-02-11',0,'scanA'),
 (8, 1,'high','2026-02-03','2026-02-23',0,'scanB'),
 (9, 2,'high','2026-02-05','2026-03-07',0,'scanA'),
 (10,3,'high','2026-02-10','2026-04-11',1,'scanA'),
 (11,1,'critical','2026-02-01','2026-02-02',0,'scanA'),
 (12,2,'critical','2026-02-03','2026-02-06',0,'scanA'),
 (13,3,'critical','2026-02-05','2026-02-12',1,'scanB'),
 (14,1,'high','2026-03-01',NULL,0,'scanA');

Query 1: P90 days to remediate by severity (nearest-rank method)

WITH done AS (
  SELECT severity, julianday(fixed_at) - julianday(discovered_at) AS days
  FROM vulnerabilities WHERE fixed_at IS NOT NULL),
ranked AS (
  SELECT severity, days,
         ROW_NUMBER() OVER (PARTITION BY severity ORDER BY days) AS rn,
         COUNT(*)     OVER (PARTITION BY severity)               AS n
  FROM done)
SELECT severity, n AS fixed_count, days AS p90_days
FROM ranked
WHERE rn = (9 * n + 9) / 10      -- integer form of ceil(0.9 * n)
ORDER BY severity;

Output:

severity  fixed_count  p90_days
critical  3            7.0
high      10           30.0

Check by hand: the 10 fixed "high" durations sorted are 2, 5, 6, 10, 10, 12, 20, 20, 30, 60 days; rank ceil(0.9 x 10) = 9 gives 30. Critical: 1, 3, 7 with n = 3, rank 3 gives 7. The single 60-day outlier does not move P90 (a mean would be 17.5), which is exactly why P90 is used for SLA (service-level agreement, the fix-by deadline) reporting. In PostgreSQL you would write percentile_cont(0.9) WITHIN GROUP (ORDER BY days), which interpolates between ranks, so results can differ slightly from nearest-rank.

Query 2: reopen rate by asset classification

SELECT a.classification,
       COUNT(*) AS closed_at_least_once,
       SUM(v.reopened_count > 0) AS reopened,
       ROUND(1.0 * SUM(v.reopened_count > 0) / COUNT(*), 3) AS reopen_rate
FROM vulnerabilities v JOIN assets a ON a.id = v.asset_id
WHERE v.fixed_at IS NOT NULL OR v.reopened_count > 0
GROUP BY a.classification ORDER BY a.classification;

classification  closed_at_least_once  reopened  reopen_rate
internal        5                     0         0.0
restricted      8                     4         0.5

The 1.0 * forces decimal division (integer division would return 0). Restricted assets reopen at 4 of 8 here, which would prompt a question about whether fixes there are being verified by rescan before closure.

Query 3: mean time between recurrences on the same asset

WITH gaps AS (
  SELECT asset_id,
         julianday(discovered_at) -
         julianday(LAG(discovered_at) OVER (PARTITION BY asset_id ORDER BY discovered_at)) AS gap_days
  FROM vulnerabilities)
SELECT asset_id, COUNT(gap_days) AS gaps, ROUND(AVG(gap_days), 2) AS mean_gap_days
FROM gaps GROUP BY asset_id HAVING COUNT(gap_days) > 0 ORDER BY asset_id;

asset_id  gaps  mean_gap_days
1         4     14.75
2         4     7.0
3         3     9.67

Asset 1 was found on Jan 1, Jan 5, Feb 1, Feb 3, Mar 1: gaps 4, 27, 2, 26, sum 59, mean 59 / 4 = 14.75. LAG looks at the previous row for the same asset; the first row has no predecessor (NULL), and AVG skips NULLs, so an asset's first finding, which has nothing before it, simply adds no gap.

Pitfalls and edge cases

• Small n: with 3 critical findings, P90 is simply the maximum. Report fixed_count beside every percentile.
• Gaming: closing tickets without a rescan lowers time to remediate; the reopen rate is the counter-metric, so publish them together.
• Multiple scanners can report the same weakness twice, inflating recurrences; deduplicate on a stable key before trusting query 3.
• Timestamps stored as text in mixed formats or time zones break julianday; normalize to UTC ISO 8601 first.
• Complexity: each query is one pass plus a sort per partition, O(n log n).

Direct answer

Filter to open tickets older than 90 days, then keep only the tickets that have no matching row in evidence (an anti-join: a join that keeps the rows with no partner), then group by service for the count and oldest date. Use NOT EXISTS (or LEFT JOIN ... IS NULL), not a plain join, because a ticket with several evidence rows would otherwise be counted several times.

What the words mean. A remediation ticket is a work item to fix a security weakness that was found. Evidence is proof attached to it that the fix happened, such as a rescan report, a change record or a screenshot. NOT EXISTS (SELECT 1 FROM evidence e WHERE e.ticket_id = t.id) means: for each ticket row, run the inner SELECT looking for any evidence row with that ticket's id, and keep the ticket only if it finds none.

Assumptions to state

• tickets has no type column, so I assume every row is a security remediation ticket. If a ticket_type or label column existed I would add AND t.ticket_type = 'security_remediation'.
• "Open" means status open or in_progress; closed is finished. Confirm the real status vocabulary before shipping.
• "Older than 90 days" is measured from created_at (not due_date) against a fixed as-of date, so the result is reproducible. In production replace the literal with the current date.
• "No evidence attached" means no row in evidence for that ticket, of any evidence_type.

Sample data and query (run in SQLite 3.51)

CREATE TABLE tickets (id INTEGER PRIMARY KEY, service TEXT, owner TEXT, status TEXT,
                      created_at TEXT, due_date TEXT);
CREATE TABLE evidence (evidence_id INTEGER PRIMARY KEY, ticket_id INTEGER,
                       evidence_type TEXT, created_at TEXT);

INSERT INTO tickets VALUES
 (1,'billing','ana','open','2026-05-01','2026-06-01'),
 (2,'billing','ana','open','2026-06-20','2026-07-20'),
 (3,'billing','raj','open','2026-04-10','2026-05-10'),
 (4,'search','li','open','2026-03-15','2026-04-15'),
 (5,'search','li','closed','2026-03-01','2026-04-01'),
 (6,'auth','sam','open','2026-06-01','2026-07-01'),
 (7,'auth','sam','open','2026-09-01','2026-10-01'),
 (8,'auth','sam','in_progress','2026-05-20','2026-06-20');

INSERT INTO evidence VALUES
 (1,3,'rescan_report','2026-08-01'),
 (2,3,'screenshot','2026-08-02'),
 (3,6,'change_record','2026-07-15');

SELECT t.service,
       COUNT(*)          AS open_count,
       MIN(t.created_at) AS oldest_ticket
FROM tickets t
WHERE t.status IN ('open', 'in_progress')
  AND julianday('2026-09-28') - julianday(t.created_at) > 90
  AND NOT EXISTS (SELECT 1 FROM evidence e WHERE e.ticket_id = t.id)
GROUP BY t.service
ORDER BY open_count DESC, oldest_ticket;

service  open_count  oldest_ticket
billing  2           2026-05-01
search   1           2026-03-15
auth     1           2026-05-20

Walking the sample rows

• As of 2026-09-28, the 90-day cutoff is 2026-06-30, so tickets created after that are too young (ticket 7).
• billing: tickets 1 and 2 are open, old and have no evidence (count 2). Ticket 3 is old but has two evidence rows, so it drops out.
• search: ticket 4 qualifies; ticket 5 is closed.
• auth: ticket 6 has evidence, ticket 7 is too new, ticket 8 (in_progress, created 05-20) qualifies.

Why not the plain join

A LEFT JOIN ... WHERE e.ticket_id IS NULL also gives the right answer. But a plain join (or a LEFT JOIN without that filter) produces one output row per evidence row: ticket 3 has two evidence rows, so it appears twice. Billing's rows would then be ticket 1 (1 row) + ticket 2 (1 row) + ticket 3 (2 rows) = 4, instead of the correct 2, and ticket 3 should not count at all because it has evidence. NOT EXISTS never multiplies rows and stops at the first matching evidence row.

Why not NOT IN? WHERE t.id NOT IN (SELECT ticket_id FROM evidence) breaks if any ticket_id is NULL (an unknown value). Take evidence ticket ids (1, NULL) and ticket 2: 2 NOT IN (1, NULL) means 2<>1 AND 2<>NULL; the second part is unknown, not true, so the whole test is unknown and the ticket is dropped. Run in SQLite, that query returned 0 rows where NOT EXISTS returned the 1 expected row. NOT EXISTS is immune to this.

Pitfalls

• Text dates must be ISO 8601 (YYYY-MM-DD) for julianday and for MIN to sort correctly.
• An index on evidence(ticket_id) keeps the anti-join cheap as the table grows.
• Junk evidence (an empty file) defeats the check; the query proves presence, not quality. Pair it with a review step for the oldest tickets.