Skip to content

The OSINT investigator's workbench. One case, one folder, every tool. Local-first: your media and your investigations never leave your machine.

JavaScript
18.3 KB
Raw Blame
"""A sheet built out of the proofs the case already holds, and kept level with them.

The `geoloc` template and this shape carry almost the same columns and run in opposite
directions, which is the confusion these tests exist to prevent. The template is
**incoming**: addresses pasted in, pressed, downloaded, turned into proofs. This is
**outgoing**: proofs the case already established, laid out as a table to read, filter
and export. One holds the text of URLs, the other holds links to entities.

What each test is really guarding:

- one row per proof, and three links on it, so every proof, media and place is reachable
  from the sheet and the sheet from each of them;
- an incomplete proof still gets its row — a proof nobody placed is information, not an
  error, and dropping it would let the build decide which proofs count;
- the coordinates come off the graph on every read, so the sheet never holds a stale
  copy of a place somebody moved;
- pressing refresh adds and never removes, because the notes on a row are the analyst's;
- a proof deleted afterwards leaves its row standing and says so in `In case`, which is
  the one thing the ordinary dead-link sweep cannot express.
"""

from azimut.engine import sheetfromcase as fromcase_engine

from test_sheet_bridge import add, make_case, post_sheet, read_sheet


def get_case_(case_id):
    from azimut.api.cases import get_case

    return get_case(case_id)


def link(case_id, from_id, to_id, type_):
    get_case_(case_id).add_link(from_id, to_id, type_, by="test")


def a_proof(client, case_id, label, *, media=None, place=None):
    """A proof, and the two edges the outgoing shape reads off it."""
    proof = add(client, case_id, "proof", label)["id"]
    if media:
        link(case_id, proof, media, "derived-from")
    if place:
        link(case_id, proof, place, "depicts")
    return proof


def build(client, case_id, title="My geolocations"):
    made = client.post(
        f"/api/cases/{case_id}/sheets/from-case",
        json={"title": title, "shape": "proofs"},
    )
    assert made.status_code == 200, made.text
    return made.json()


COLUMNS = [
    "id", "Title", "Date", "Place", "POV", "Coordinates", "Source media", "Source URL",
    "Description", "In case", "Status", "Notes",
]


def cell(sheet, row, name):
    """One cell by its column's name, so a test reads the way the sheet does."""
    return sheet["rows"][row][sheet["columns"].index(name)]


def put_cell(sheet, row, name, value):
    sheet["rows"][row][sheet["columns"].index(name)] = value


# -- the shape ----------------------------------------------------------------


def test_one_row_per_proof_carrying_its_media_and_its_place(client):
    case_id = make_case(client)
    media = add(client, case_id, "media", "GX010234")["id"]
    place = add(client, case_id, "place", "Rooftop", lat=47.1, lon=37.5)["id"]
    proof = a_proof(client, case_id, "Rooftop shot", media=media, place=place)

    made = build(client, case_id)
    assert made["taken"] == 1 and made["total"] == 1

    sheet = read_sheet(client, case_id, made["id"])
    assert sheet["columns"] == COLUMNS
    assert [cell(sheet, 0, name) for name in ("Title", "Source media", "Place")] == [
        "Rooftop shot", "GX010234", "Rooftop",
    ]
    # Filled off the graph rather than copied in by hand, which is what keeps it true.
    assert cell(sheet, 0, "Coordinates") == "47.10000, 37.50000"
    assert cell(sheet, 0, "In case") == "YES"
    # Every line is a proof that exists: `to do` on all of them would be a lie.
    assert cell(sheet, 0, "Status") == "done"

    key = sheet["rows"][0][0]
    assert sheet["meta"]["links"][key] == {
        "Title": proof,
        "Source media": media,
        "Place": place,
    }
    assert sheet["meta"]["built"][key] == proof
    assert sheet["meta"]["progress"] == "Status"


def test_the_three_case_columns_are_the_apps_and_the_two_work_columns_are_not(client):
    case_id = make_case(client)
    a_proof(client, case_id, "Rooftop shot")

    sheet = read_sheet(client, case_id, build(client, case_id)["id"])
    roles = sheet["meta"]["roles"]
    for column in ("Title", "Date", "Place", "POV", "Source media", "Source URL", "Description"):
        assert roles[column]["kind"] == "locked", column
    assert roles["Coordinates"] == {"kind": "computed", "of": "point", "from": "Place"}
    assert roles["In case"]["of"] == "in_case"
    assert roles["Status"]["kind"] == "state"
    # The analyst's own two carry no role the app writes through.
    assert "Notes" not in roles


def test_a_proof_with_no_place_still_gets_its_row(client):
    case_id = make_case(client)
    media = add(client, case_id, "media", "GX010234")["id"]
    a_proof(client, case_id, "Unplaced", media=media)

    sheet = read_sheet(client, case_id, build(client, case_id)["id"])
    assert [cell(sheet, 0, name) for name in ("Title", "Source media", "Place", "Coordinates")] == [
        "Unplaced", "GX010234", "", "",
    ]
    key = sheet["rows"][0][0]
    assert "Place" not in sheet["meta"]["links"][key]


def test_a_proof_with_no_media_still_gets_its_row(client):
    case_id = make_case(client)
    place = add(client, case_id, "place", "Rooftop", lat=47.1, lon=37.5)["id"]
    a_proof(client, case_id, "Sourceless", place=place)

    sheet = read_sheet(client, case_id, build(client, case_id)["id"])
    assert [cell(sheet, 0, name) for name in ("Title", "Source media", "Place", "Coordinates")] == [
        "Sourceless", "", "Rooftop", "47.10000, 37.50000",
    ]
    key = sheet["rows"][0][0]
    assert "Source media" not in sheet["meta"]["links"][key]


def test_a_proof_on_several_medias_takes_one_and_the_same_one_twice(client):
    case_id = make_case(client)
    proof = add(client, case_id, "proof", "Many sources")["id"]
    # Filed in an order that is not the label order, so a build reading insertion order
    # would answer differently from one reading the labels.
    for label in ("zulu.mp4", "alpha.mp4", "mike.mp4"):
        link(case_id, proof, add(client, case_id, "media", label)["id"], "derived-from")

    first = read_sheet(client, case_id, build(client, case_id, "One")["id"])
    second = read_sheet(client, case_id, build(client, case_id, "Two")["id"])
    assert cell(first, 0, "Source media") == "alpha.mp4"
    assert cell(second, 0, "Source media") == "alpha.mp4"


def test_a_proof_placed_at_three_points_is_three_rows(client):
    case_id = make_case(client)
    proof = add(client, case_id, "proof", "Three strikes", when="2026-01-03~",
                notes="Three impacts filmed from one rooftop.")["id"]
    for label, lat in (("impact 2", 47.2), ("impact 1", 47.1), ("POV", 47.3)):
        place = add(client, case_id, "place", label, lat=lat, lon=37.5)["id"]
        link(case_id, proof, place, "depicts")

    made = build(client, case_id)
    sheet = read_sheet(client, case_id, made["id"])

    # The limit counts proofs, the rows are the points.
    assert made["taken"] == 1
    assert [cell(sheet, row, "Place") for row in range(3)] == ["impact 1", "impact 2", "POV"]
    assert {cell(sheet, row, "Title") for row in range(3)} == {"Three strikes"}
    assert {cell(sheet, row, "Date") for row in range(3)} == {"2026-01-03~"}
    assert {cell(sheet, row, "Description") for row in range(3)} == {
        "Three impacts filmed from one rooftop."
    }
    assert set(sheet["meta"]["built"].values()) == {proof}


def test_pov_says_where_the_camera_stood_and_the_source_url_is_the_post(client):
    case_id = make_case(client)
    video = add(client, case_id, "media", "clip.mp4",
                source_url="https://x.com/someone/status/1")["id"]
    frame = add(client, case_id, "media", "frame")["id"]
    link(case_id, frame, video, "derived-from")
    camera = add(client, case_id, "place", "Rooftop", lat=47.1, lon=37.5)["id"]
    target = add(client, case_id, "place", "Bridge", lat=47.11, lon=37.5)["id"]
    proof = a_proof(client, case_id, "Bridge from the roof", media=frame, place=camera)
    link(case_id, proof, target, "depicts")
    # What the composer writes on the footage: recorded where the camera stood.
    link(case_id, video, camera, "located-at")
    link(case_id, video, target, "depicts")

    sheet = read_sheet(client, case_id, build(client, case_id)["id"])
    by_place = {cell(sheet, row, "Place"): row for row in range(len(sheet["rows"]))}

    assert cell(sheet, by_place["Rooftop"], "POV") == "YES"
    assert cell(sheet, by_place["Bridge"], "POV") == "NO"
    assert cell(sheet, by_place["Bridge"], "Source URL") == "https://x.com/someone/status/1"


def test_refresh_adds_a_point_added_to_a_proof_and_keeps_the_others(client):
    case_id = make_case(client)
    first = add(client, case_id, "place", "impact 1", lat=47.1, lon=37.5)["id"]
    proof = a_proof(client, case_id, "Strikes", place=first)
    sheet = read_sheet(client, case_id, build(client, case_id)["id"])
    put_cell(sheet, 0, "Notes", "checked")
    second = add(client, case_id, "place", "impact 2", lat=47.2, lon=37.5)["id"]
    link(case_id, proof, second, "depicts")

    answer = post_sheet(client, case_id, sheet["id"], "refresh", sheet)

    assert answer.status_code == 200, answer.text
    assert answer.json()["added"] == 1
    fresh = read_sheet(client, case_id, sheet["id"])
    assert [cell(fresh, row, "Place") for row in range(2)] == ["impact 1", "impact 2"]
    assert cell(fresh, 0, "Notes") == "checked"


def test_a_sheet_built_with_the_old_columns_keeps_them_on_refresh(client):
    case_id = make_case(client)
    place = add(client, case_id, "place", "Rooftop", lat=47.1, lon=37.5)["id"]
    a_proof(client, case_id, "Rooftop shot", place=place)
    sheet = read_sheet(client, case_id, build(client, case_id)["id"])
    # The shape before the per-point rows: eight columns, no date, no description.
    old = ["id", "Title", "Source media", "Place", "Coordinates", "In case", "Status", "Notes"]
    sheet["rows"] = [[cell(sheet, 0, name) for name in old]]
    sheet["columns"] = old
    sheet["meta"]["roles"] = {
        name: role for name, role in sheet["meta"]["roles"].items() if name in old
    }

    answer = post_sheet(client, case_id, sheet["id"], "refresh", sheet)

    assert answer.status_code == 200, answer.text
    assert answer.json()["added"] == 0
    assert answer.json()["columns"] == old


def test_rows_are_ordered_by_the_proofs_label(client):
    case_id = make_case(client)
    for label in ("Zulu", "Alpha", "Mike"):
        a_proof(client, case_id, label)

    sheet = read_sheet(client, case_id, build(client, case_id)["id"])
    assert [row[1] for row in sheet["rows"]] == ["Alpha", "Mike", "Zulu"]


def test_the_sheet_mentions_the_proof_the_media_and_the_place(client):
    case_id = make_case(client)
    media = add(client, case_id, "media", "GX010234")["id"]
    place = add(client, case_id, "place", "Rooftop", lat=47.1, lon=37.5)["id"]
    proof = a_proof(client, case_id, "Rooftop shot", media=media, place=place)

    sheet_id = build(client, case_id)["id"]
    mentioned = {
        str(edge["to"])
        for edge in get_case_(case_id).list_links()
        if edge["type"] == "mentions" and str(edge["from"]) == sheet_id
    }
    assert mentioned == {proof, media, place}


def test_the_generic_shape_is_untouched(client):
    case_id = make_case(client)
    add(client, case_id, "person", "Ivanov", role="driver")

    made = client.post(
        f"/api/cases/{case_id}/sheets/from-case",
        json={"title": "People", "type": "person", "fields": ["role"]},
    )
    assert made.status_code == 200, made.text
    sheet = read_sheet(client, case_id, made.json()["id"])
    assert sheet["columns"] == ["id", "Name", "Role", "Status", "Notes"]


def test_a_generic_build_still_needs_a_type(client):
    case_id = make_case(client)
    refused = client.post(f"/api/cases/{case_id}/sheets/from-case", json={"title": "Nothing"})
    assert refused.status_code == 422


# -- kept level with the case -------------------------------------------------


def test_refresh_files_the_proofs_added_since_the_build(client):
    case_id = make_case(client)
    a_proof(client, case_id, "Alpha")
    sheet = read_sheet(client, case_id, build(client, case_id)["id"])
    a_proof(client, case_id, "Bravo")

    answer = post_sheet(client, case_id, sheet["id"], "refresh", sheet)
    assert answer.status_code == 200, answer.text
    assert answer.json()["added"] == 1
    assert answer.json()["gone"] == 0

    fresh = read_sheet(client, case_id, sheet["id"])
    # Appended rather than re-sorted: inserting in place reshuffles a table somebody is
    # working down, and the sheet's own sort is where order is decided.
    assert [row[1] for row in fresh["rows"]] == ["Alpha", "Bravo"]


def test_refresh_leaves_the_analysts_own_columns_alone(client):
    case_id = make_case(client)
    a_proof(client, case_id, "Alpha")
    sheet = read_sheet(client, case_id, build(client, case_id)["id"])
    put_cell(sheet, 0, "Status", "in progress")
    put_cell(sheet, 0, "Notes", "waiting on the second angle")
    saved = post_sheet(client, case_id, sheet["id"], "refresh", sheet)
    assert saved.status_code == 200, saved.text

    fresh = read_sheet(client, case_id, sheet["id"])
    assert cell(fresh, 0, "Status") == "in progress"
    assert cell(fresh, 0, "Notes") == "waiting on the second angle"


def test_refresh_restates_a_proof_renamed_since_without_filing_a_second_row(client):
    case_id = make_case(client)
    proof = a_proof(client, case_id, "Alpha")
    sheet = read_sheet(client, case_id, build(client, case_id)["id"])

    client.patch(f"/api/cases/{case_id}/entities/{proof}", json={"label": "Alpha, revised"})
    answer = post_sheet(client, case_id, sheet["id"], "refresh", sheet)
    assert answer.status_code == 200, answer.text
    assert answer.json()["added"] == 0

    fresh = read_sheet(client, case_id, sheet["id"])
    assert len(fresh["rows"]) == 1
    assert fresh["rows"][0][1] == "Alpha, revised"


def test_refresh_never_removes_a_row_and_says_the_proof_is_gone(client):
    case_id = make_case(client)
    kept = a_proof(client, case_id, "Alpha")
    dropped = a_proof(client, case_id, "Bravo")
    sheet = read_sheet(client, case_id, build(client, case_id)["id"])
    sheet_id = sheet["id"]
    put_cell(sheet, 1, "Notes", "worth keeping")
    assert post_sheet(client, case_id, sheet_id, "refresh", sheet).status_code == 200

    client.delete(f"/api/cases/{case_id}/entities/{dropped}")

    fresh = read_sheet(client, case_id, sheet_id)
    assert len(fresh["rows"]) == 2
    # The row keeps its text and the note nobody else wrote.
    assert fresh["rows"][1][1] == "Bravo"
    assert cell(fresh, 1, "Notes") == "worth keeping"
    # The link is swept, as it is on every sheet — and `built` is what survives to answer.
    assert cell(fresh, 0, "In case") == "YES"
    assert cell(fresh, 1, "In case") == "NO"
    assert "Title" not in fresh["meta"]["links"].get(fresh["rows"][1][0], {})
    assert fresh["meta"]["built"][fresh["rows"][1][0]] == dropped
    assert fresh["meta"]["built"][fresh["rows"][0][0]] == kept

    answer = post_sheet(client, case_id, sheet_id, "refresh", fresh)
    assert answer.json()["gone"] == 1
    assert answer.json()["added"] == 0


def test_a_row_nobody_built_is_left_out_of_the_in_case_column(client):
    case_id = make_case(client)
    a_proof(client, case_id, "Alpha")
    sheet = read_sheet(client, case_id, build(client, case_id)["id"])
    # A line the analyst adds for a place not yet proven: it arrives as work to do, and
    # the question `In case` asks does not apply to it.
    line = [""] * len(sheet["columns"])
    line[sheet["columns"].index("Title")] = "A place still to prove"
    line[sheet["columns"].index("Status")] = "to do"
    sheet["rows"].append(line)
    saved = client.put(
        f"/api/cases/{case_id}/sheets/{sheet['id']}",
        json={k: sheet[k] for k in ("columns", "rows", "meta", "stamp")},
    )
    assert saved.status_code == 200, saved.text

    fresh = read_sheet(client, case_id, sheet["id"])
    assert cell(fresh, 1, "In case") == ""
    assert cell(fresh, 1, "Status") == "to do"


def test_the_coordinates_follow_a_place_that_moves(client):
    case_id = make_case(client)
    place = add(client, case_id, "place", "Rooftop", lat=47.1, lon=37.5)["id"]
    a_proof(client, case_id, "Rooftop shot", place=place)
    sheet_id = build(client, case_id)["id"]

    client.patch(
        f"/api/cases/{case_id}/entities/{place}",
        json={"attrs": {"lat": 48.9, "lon": 24.7}},
    )
    fresh = read_sheet(client, case_id, sheet_id)
    assert cell(fresh, 0, "Coordinates") == "48.90000, 24.70000"


def test_the_build_is_bounded(client):
    assert fromcase_engine.MAX_FROM_CASE == 2_000


def test_refresh_is_refused_on_a_sheet_that_was_not_built_this_way(client):
    case_id = make_case(client)
    a_proof(client, case_id, "Alpha")
    # A binder that happens to hold a column called Title. Nothing stops that, and pouring
    # every proof in the case into it would file rows of empty cells nobody asked for.
    made = client.post(
        f"/api/cases/{case_id}/sheets/import",
        json={"title": "Imported", "text": "Title,Notes\nSomething,\n"},
    ).json()
    sheet = read_sheet(client, case_id, made["id"])

    refused = post_sheet(client, case_id, sheet["id"], "refresh", sheet)
    assert refused.status_code == 422
    assert "not built out of the case" in refused.text


def test_two_sheets_built_the_same_way_are_told_apart(client):
    """The name is the filename, suffix and all.

    `target` steps a taken name to `-2` rather than refusing it, and the label used to keep
    the name that was asked for — so building this shape twice left two rows reading `My
    geolocations` in the list, backed by two different files, with nothing on screen to
    say which was which.
    """
    case_id = make_case(client)
    a_proof(client, case_id, "Alpha")

    build(client, case_id)
    build(client, case_id)
    build(client, case_id)

    listed = client.get(f"/api/cases/{case_id}/sheets").json()["sheets"]
    # Sorted, because the order is not this test's business and is not stable for
    # three sheets built in the same breath: the list answers newest first, and a
    # creation stamp is second-resolution, so whether these three separate at all
    # depends on which side of a tick they land. Ordering is covered on its own
    # (test_sheets.py, the list opens on the sheet just made).
    assert sorted(entry["title"] for entry in listed) == [
        "My geolocations",
        "My geolocations-2",
        "My geolocations-3",
    ]
    # And each one is its own file, which is what the names now say.
    assert len({entry["path"] for entry in listed}) == 3