Your deletion tracker may be counting collection failures

Your change log says a broadcast disappeared, and that count is heading into a report. Now you've to defend whether the broadcast was deleted or your collection failed.

The suspicious line is usually innocent-looking: WHERE type = 'broadcast'. It keeps the entities you want to analyse and removes the evidence you need to explain their absence.

Our broadcast Actor emits status rows for invalid links, unsupported share links, Spaces, deleted broadcasts and rate limits. A missing broadcast row does not establish that the broadcast was deleted.

The missing row has another explanation

A broadcast-only comparison collapses different outcomes into the same empty space.

Keep the branches visible:

submitted target
    |
    +-- broadcast row --> compare the resolved entity
    |
    +-- status row ----> retain the diagnostic
    |
    +-- incomplete collection --> retain the failed attempt

This is an observation problem before it's a deletion problem. A tidy change log can still be confidently wrong.

Use a single submitted target per collection attempt here. That gives every diagnostic a reliable association with the attempted target, without assuming the output echoes its input or arrives in input order.

Example collection input and fetch, with optional enrichments disabled so the tracker concentrates on broadcast metadata:

import os
import json
from datetime import datetime, timezone
from uuid import uuid4

import pandas as pd
import duckdb
from apify_client import ApifyClient

run_input = {
    "broadcastIds": ["https://x.com/i/broadcasts/1YGNrbPLejQGw"],
    "includeViewerStats": False,
    "includeViewerTimeline": False,
    "maxViewerTimelinePoints": 0,
    "includeReplayVideo": False,
    "includeChatHistory": False,
    "maxChatMessages": 1000,
    "includeThumbnail": False,
    "includeBroadcasterProfile": False,
}
submitted_target = run_input["broadcastIds"][0]
options_json = json.dumps(
    {k: v for k, v in run_input.items() if k != "broadcastIds"},
    sort_keys=True,
)
run_key = str(uuid4())  # Our observation identifier.
started_at = datetime.now(timezone.utc)
items = []
run_status = "CLIENT_ERROR"  # Our label, not a platform run status.
dataset_complete = False
error = None

try:
    client = ApifyClient(os.environ["APIFY_TOKEN"])
    run = client.actor(
        "dataspryva/x-broadcast-viewer-history-and-replay-link-scraper"
    ).call(run_input=run_input)
    run_status = run["status"]
    for item in client.dataset(run["defaultDatasetId"]).iterate_items():
        items.append(item)
    dataset_complete = True
except Exception as exc:
    error = str(exc)

finished_at = datetime.now(timezone.utc)

Reading the whole dataset matters. A partially retrieved dataset is another way to manufacture an absence, even when the remote run finished successfully.

The wrapper retains partial results, but it won't promote them into successful snapshots.

Keep identity separate from the route in

A submitted URL identifies your collection target. The broadcast row's id identifies the resolved broadcast.

Those are different jobs.

Legacy /v/<id> and /<user>/<id> links resolve through their trailing broadcast ID. An announcement post can also lead to a broadcast. Changing the route into an entity mustn't create a new entity in your change log.

Keep these distinctions in the stored data:

Fields	Treatment in this tracker
Broadcast id	Entity key, paired with the collection's run_key for snapshot uniqueness
state, title	Candidate substantive changes when present in comparable successful observations
Broadcast status	Published machine state, kept separate from diagnostic rows
sourceTweetId, sourceTweetUrl, announcementTweetId, announcementTweetUrl	Discovery and announcement context, preserved outside the entity key
collectedAt	Observation timestamp, excluded from substantive field comparisons
Status-row status, message	Diagnostic evidence, retained verbatim

Don't substitute broadcastId whenever id is missing. Timeline and chat rows use broadcastId; this tracker deliberately selects broadcast records.

The input route stays in a target ledger. The resolved identity belongs beside the mutable fields.

Store the attempt before interpreting the result

Use a local database that persists between notebook sessions. Store the complete returned payload alongside the collection outcome, then materialise successful broadcast snapshots separately.

The review labels below belong to our tracker. They aren't assumed Actor status values.

Create the storage and the diagnostic view:

con = duckdb.connect("broadcast-tracker.duckdb")
con.execute("""
CREATE TABLE IF NOT EXISTS target_ledger (
    submitted_target VARCHAR PRIMARY KEY,
    expected_id VARCHAR
);
CREATE TABLE IF NOT EXISTS collection_runs (
    run_key VARCHAR PRIMARY KEY,
    submitted_target VARCHAR,
    started_at TIMESTAMPTZ,
    finished_at TIMESTAMPTZ,
    run_status VARCHAR,
    dataset_complete BOOLEAN,
    error VARCHAR,
    options_json VARCHAR,
    payload_json JSON
);
CREATE TABLE IF NOT EXISTS broadcast_snapshots (
    run_key VARCHAR,
    id VARCHAR,
    collected_at TIMESTAMPTZ,
    state VARCHAR,
    state_present BOOLEAN,
    raw_json JSON,
    PRIMARY KEY (id, run_key)
);
CREATE TABLE IF NOT EXISTS status_reviews (
    run_key VARCHAR,
    row_key VARCHAR,
    category VARCHAR CHECK (
        category IN (
            'failed_observation',
            'source_reported_unavailability'
        )
    ),
    PRIMARY KEY (run_key, row_key)
);
CREATE VIEW IF NOT EXISTS status_observations AS
SELECT
    c.run_key,
    j.key AS row_key,
    json_extract_string(j.value, '$.status') AS status,
    json_extract_string(j.value, '$.message') AS message,
    j.value AS raw_json
FROM collection_runs AS c, json_each(c.payload_json) AS j
WHERE json_extract_string(j.value, '$.type') = 'status';
""")

Keeping the payload also preserves omitted keys, provenance and any returned record types your current analysis doesn't use. Future interpretation shouldn't require rewriting historical evidence.

Write the collection and its eligible snapshots in a transaction:

run_frame = pd.DataFrame([{
    "run_key": run_key,
    "submitted_target": submitted_target,
    "started_at": started_at,
    "finished_at": finished_at,
    "run_status": run_status,
    "dataset_complete": dataset_complete,
    "error": error,
    "options_json": options_json,
    "payload_json": json.dumps(items),
}])
con.register("incoming_run", run_frame)

con.execute("BEGIN TRANSACTION")
try:
    con.execute("""
        INSERT INTO target_ledger VALUES (?, NULL)
        ON CONFLICT DO NOTHING
    """, [submitted_target])
    con.execute("""
        INSERT INTO collection_runs
        SELECT
            run_key, submitted_target, started_at, finished_at,
            run_status, dataset_complete, error, options_json,
            CAST(payload_json AS JSON)
        FROM incoming_run
    """)
    con.execute("""
        INSERT INTO broadcast_snapshots
        SELECT
            c.run_key,
            json_extract_string(j.value, '$.id'),
            TRY_CAST(
                json_extract_string(j.value, '$.collectedAt')
                AS TIMESTAMPTZ
            ),
            json_extract_string(j.value, '$.state'),
            json_exists(j.value, '$.state'),
            j.value
        FROM collection_runs AS c, json_each(c.payload_json) AS j
        WHERE c.run_key = ?
          AND c.run_status = 'SUCCEEDED'
          AND c.dataset_complete
          AND json_extract_string(j.value, '$.type') = 'broadcast'
          AND NULLIF(json_extract_string(j.value, '$.id'), '') IS NOT NULL
    """, [run_key])
    con.execute("COMMIT")
except Exception:
    con.execute("ROLLBACK")
    raise

Empty fields are omitted from the Actor's records. The state_present flag therefore matters: an absent state is missing evidence, not a transition to an empty state.

TRY_CAST parses the returned timestamp string without inventing a replacement when it can't be parsed. Collection start and finish times remain available independently.

Bind identity after successful resolution

A failed first attempt can't establish a broadcast identity.

Bind the submitted target only when its completed collection resolves to an unambiguous broadcast ID. Preserve that binding on subsequent attempts so a changed resolution becomes something to investigate.

This statement establishes the initial association:

con.execute("""
UPDATE target_ledger AS t
SET expected_id = (
    SELECT min(id)
    FROM broadcast_snapshots
    WHERE run_key = ?
)
WHERE t.submitted_target = ?
  AND t.expected_id IS NULL
  AND (
      SELECT count(DISTINCT id)
      FROM broadcast_snapshots
      WHERE run_key = ?
  ) = 1
""", [run_key, submitted_target, run_key])

Different submitted links can now bind to the same canonical identity. Their discovery context stays separate, while their successful observations can share an entity history.

For a larger batch, keep diagnostics unmatched unless the returned data supplies a reliable association. A message mentioning a problem isn't permission to attach it to whichever broadcast happens to be missing.

Compare states only across usable observations

A useful diff asks whether the entity changed and whether the observations support that comparison.

The query below compares the current attempt with the last successful observation that finished before this attempt began. It also checks collection options, field presence and unexpected identity changes.

Run the comparison for the current collection:

diff_sql = """
WITH diagnostics AS (
    SELECT
        s.run_key,
        count(*) AS diagnostic_count,
        bool_or(
            r.category = 'source_reported_unavailability'
        ) AS reported_unavailable
    FROM status_observations AS s
    LEFT JOIN status_reviews AS r
        USING (run_key, row_key)
    GROUP BY s.run_key
)
SELECT
    c.run_key,
    c.submitted_target,
    t.expected_id,
    p.collected_at AS last_success_collected_at,
    p.finished_at AS last_success_finished_at,
    p.state AS previous_state,
    n.state AS current_state,
    CASE
        WHEN c.run_status <> 'SUCCEEDED' OR NOT c.dataset_complete
            THEN 'collection_failed'
        WHEN t.expected_id IS NULL
            THEN 'identity_unresolved'
        WHEN EXISTS (
            SELECT 1 FROM broadcast_snapshots AS other
            WHERE other.run_key = c.run_key
              AND other.id <> t.expected_id
        ) THEN 'resolved_identity_changed'
        WHEN n.id IS NULL AND coalesce(d.reported_unavailable, false)
            THEN 'source_reported_unavailability'
        WHEN n.id IS NULL AND coalesce(d.diagnostic_count, 0) > 0
            THEN 'failed_observation'
        WHEN n.id IS NULL
            THEN 'no_broadcast_observed'
        WHEN p.id IS NULL
            THEN 'first_success'
        WHEN c.options_json IS DISTINCT FROM p.options_json
            THEN 'configuration_changed'
        WHEN NOT n.state_present OR NOT p.state_present
            THEN 'state_not_comparable'
        WHEN n.state IS DISTINCT FROM p.state
            THEN 'observed_state_change'
        ELSE 'state_unchanged'
    END AS change_category
FROM collection_runs AS c
JOIN target_ledger AS t USING (submitted_target)
LEFT JOIN broadcast_snapshots AS n
    ON n.run_key = c.run_key AND n.id = t.expected_id
LEFT JOIN diagnostics AS d ON d.run_key = c.run_key
LEFT JOIN LATERAL (
    SELECT s.*, earlier.finished_at, earlier.options_json
    FROM broadcast_snapshots AS s
    JOIN collection_runs AS earlier USING (run_key)
    WHERE s.id = t.expected_id
      AND earlier.finished_at < c.started_at
    ORDER BY earlier.finished_at DESC, s.run_key DESC
    LIMIT 1
) AS p ON true
WHERE c.run_key = ?
"""
changes = con.execute(diff_sql, [run_key]).df()

There's deliberately no deleted category.

source_reported_unavailability requires an explicit review label attached to a particular diagnostic. Until then, the query treats a diagnostic without a successful entity observation as a failed observation.

The stored payload supports broader diffs later. It doesn't make every field a useful change signal. A new collection timestamp is bookkeeping; a different submitted post is provenance; an omitted optional field isn't evidence that the broadcast lost that attribute.

Choose the substantive fields before counting changes. Comparing every JSON byte is an excellent way to build a busy dashboard.

Put the explanation beside the last success

Your manager needs the evidence behind the label, not just another categorical column.

Build a review frame containing the previous successful observation, the current diagnostic and any adjudication:

con.register("current_changes", changes)
review = con.execute("""
SELECT
    c.*,
    s.row_key,
    s.status AS diagnostic_status,
    s.message AS diagnostic_message,
    s.raw_json AS diagnostic_record,
    r.category AS reviewed_category,
    a.run_status,
    a.dataset_complete,
    a.started_at,
    a.finished_at,
    a.error
FROM current_changes AS c
JOIN collection_runs AS a USING (run_key)
LEFT JOIN status_observations AS s USING (run_key)
LEFT JOIN status_reviews AS r
    ON r.run_key = s.run_key AND r.row_key = s.row_key
""").df()
con.close()

Populate status_reviews only after inspecting the preserved diagnostic. Use source_reported_unavailability when the message explicitly reports that condition, and retain its wording so the attribution remains inspectable.

Don't infer deletion from an unfamiliar status spelling, a substring match or the absence of a replay link.

Old pscp.tv/w share links, for example, return legacyShareLinkUnsupported. That establishes an unsupported route, not the fate of a broadcast you hoped the route would identify.

The observation still has boundaries

The worked example uses X Broadcast Viewer History & Replay Link Scraper. The reusable distinction is between a change in an entity and a change in your ability to observe it.

Even an explicit unavailable response can't establish who removed the broadcast or why. Preserve it as a source report, with the collection context attached.

The last successful observation and a later failure also don't supply an exact disappearance time. Your collection schedule bounds what you can say; an unresolved attempt may not narrow that interval at all.

A defensible report can say when you last observed the broadcast and what the next attempt returned.

Your missing row can't testify about who deleted it.