clanker-saddle/test/bundles/projectdelete.py

"""The projectdelete bundle: admin-only project deletion with a STRICTLY
ownership-based cascade. The deletion takes the project row and the rows
it owns (its tasks, its goals, and those rows' children); rows in OTHER
projects that merely reference the deleted project (co-developed /
reference links, stack membership) are NEVER taken — only the pointer
rows naming it are tidied away. Covers the API wire (auth, 404, the
live-task guard), the cascade's correctness, the cross-project survival
law, standalone-task immunity, and the page's typed-name confirmation.
"""
from harness import *  # noqa: F401,F403 — the shared wire helpers


def sql_one(query, params=()):
    """The first column of the first row, or None."""
    conn = db()
    try:
        row = conn.execute(query, params).fetchone()
        return row[0] if row is not None else None
    finally:
        conn.close()


def sql_all(query, params=()):
    conn = db()
    try:
        return conn.execute(query, params).fetchall()
    finally:
        conn.close()


def sql_exec(*statements):
    """Runs (sql, params...) statements in one commit; params are the
    trailing tuple entry when present."""
    conn = db()
    try:
        for statement in statements:
            sql = statement[0]
            params = statement[1] if len(statement) > 1 else ()
            if not isinstance(params, (tuple, list)):
                params = (params,)
            conn.execute(sql, tuple(params))
        conn.commit()
    finally:
        conn.close()


def make_project(name):
    """Creates a project row straight in the DB, returning its id."""
    sql_exec(("insert into projects (name, git_url, default_branch, created_at)"
              " values (?, ?, 'main', strftime('%s','now'))",
              (name, "https://example.com/%s.git" % name)))
    return int(sql_one("select id from projects where name = ?", (name,)))


def delete_api(auth, project_id):
    """DELETE /api/projects/{id} with the caller's statum session."""
    status, body = http("DELETE", "/api/projects/%d" % project_id, None, {
        "X-Statum-Slot": "key=%s; as_at=%s" % (auth["key"], auth["as_at"])})
    return status, parse_json(body)


def parse_json(text):
    """A JSON object body, or {} when the body is empty or not JSON."""
    try:
        parsed = json.loads(text)
        return parsed if isinstance(parsed, dict) else {}
    except (ValueError, TypeError):
        return {}


def run(ctx):
    auth, fake = ctx.auth, ctx.fake

    print("== the API wire: auth, 404, method ==")
    # Unauthenticated: no slot header at all.
    status, body = http("DELETE", "/api/projects/1")
    body = parse_json(body)
    check(status == 401 and body.get("ok") is False,
          "DELETE without a session answers 401 JSON")

    # Unknown project id: 404 for the admin.
    status, body = delete_api(auth, 999999)
    check(status == 404 and "no project exists" in body.get("error", "").lower(),
          "DELETE of an unknown project answers 404")

    # A non-admin user (created through the users page) is refused 403
    # even with a genuine session.
    users_state = slot_public(auth, "/users", "users")
    act(auth, users_state["create"], {
        "username": "nonadmin", "email": "nonadmin@example.com",
        "forename": "Non", "surname": "Admin",
        "date_of_birth": "1990-01-01",
        "password": "nonadmin1", "confirm_password": "nonadmin1"})
    plain = login("nonadmin", "nonadmin1")
    victim_id = make_project("auth-probe")
    status, body = delete_api(plain, victim_id)
    check(status == 403, "DELETE by a non-admin answers 403")
    check(sql_one("select count(*) from projects where id = ?", (victim_id,)) == 1,
          "the refused DELETE left the project row alone")
    sql_exec(("delete from projects where id = ?", (victim_id,)))

    print("== cascade: the project and its OWNED rows go, in full ==")
    # The doomed project owns: two plain tasks (with comments, blocks,
    # rule attachments, capability requirements, sessions + events) and
    # one goal (with a goal-run task, goal links, goal-scoped events).
    doomed = make_project("doomed")
    keeper = make_project("keeper")
    other = make_project("other-host")     # hosts cross-project survivors
    standalone_host = make_project("standalone-host")

    sql_exec(
        ("insert into tasks (project_id, goal_id, kind, mode, creator, title,"
         " description, status, priority, created_at)"
         " values (?, 0, 'task', 'implement', 'human', 'Doomed draft',"
         " 'x', 'draft', 5, strftime('%s','now'))", (doomed,)),
        ("insert into tasks (project_id, goal_id, kind, mode, creator, title,"
         " description, status, priority, created_at)"
         " values (?, 0, 'task', 'implement', 'human', 'Doomed done',"
         " 'x', 'done', 5, strftime('%s','now'))", (doomed,)),
        ("insert into goals (project_id, title, brief, branch_name, status,"
         " creator, created_at)"
         " values (?, 'Doomed goal', 'Everything goes.', 'goal/doomed',"
         " 'complete', 'human', strftime('%s','now'))", (doomed,)),
    )
    doomed_goal = int(sql_one("select id from goals where project_id = ?", (doomed,)))
    sql_exec(
        # The goal's own goal-run task (owned via project_id AND goal_id).
        ("insert into tasks (project_id, goal_id, kind, mode, creator, title,"
         " description, status, priority, created_at)"
         " values (?, ?, 'goal_run', 'orchestrate', 'goal', 'Goal run',"
         " 'x', 'done', 3, strftime('%s','now'))", (doomed, doomed_goal)),
        # A goal_links row on the doomed goal pointing at `keeper`.
        ("insert into goal_links (goal_id, project_id, relationship)"
         " values (?, ?, 'reference')", (doomed_goal, keeper)),
    )
    doomed_tasks = [int(row[0]) for row in sql_all(
        "select id from tasks where project_id = ?", (doomed,))]

    # Child rows of the first doomed task: comment, block, rule attach,
    # capability attach, a session and events (session-carried AND
    # task-scoped).
    first_task = doomed_tasks[0]
    sql_exec(
        ("insert into rules (name, body, created_at)"
         " values ('doomed-rule', 'body', strftime('%s','now'))",),
        ("insert into capabilities (name, created_at)"
         " values ('doomed-cap', strftime('%s','now'))",),
        ("insert into task_comments (task_id, author, body, created_at)"
         " values (?, 'agent', 'a comment', strftime('%s','now'))", (first_task,)),
        ("insert into task_blocks (task_id, question, created_at)"
         " values (?, 'asked?', strftime('%s','now'))", (first_task,)),
        ("insert into task_rules (rule_id, task_id, created_at)"
         " values ((select id from rules where name = 'doomed-rule'), ?,"
         " strftime('%s','now'))", (first_task,)),
        ("insert into task_capabilities (capability_id, task_id)"
         " values ((select id from capabilities where name = 'doomed-cap'), ?)",
         (first_task,)),
        ("insert into saddles (name, host, status, token)"
         " values ('doomed-saddle', '127.0.0.1:9', 'idle', 't')",),
        ("insert into sessions (task_id, saddle_id, started_at)"
         " values (?, (select id from saddles where name = 'doomed-saddle'),"
         " strftime('%s','now'))", (first_task,)),
    )
    session_id = int(sql_one("select id from sessions where task_id = ?", (first_task,)))
    sql_exec(
        ("insert into events (session_id, seq, kind, actor, scope_type,"
         " scope_id, payload, created_at)"
         " values (?, 1, 'turn_text', 'model', 'task', ?, '{}',"
         " strftime('%s','now'))", (session_id, first_task)),
        ("insert into events (session_id, seq, kind, actor, scope_type,"
         " scope_id, payload, created_at)"
         " values (null, null, 'comment', 'user', 'task', ?, '{}',"
         " strftime('%s','now'))", (first_task,)),
        ("insert into events (session_id, seq, kind, actor, scope_type,"
         " scope_id, payload, created_at)"
         " values (null, null, 'state_change', 'user', 'goal', ?, '{}',"
         " strftime('%s','now'))", (doomed_goal,)),
        ("insert into events (session_id, seq, kind, actor, scope_type,"
         " scope_id, payload, created_at)"
         " values (null, null, 'state_change', 'user', 'project', ?, '{}',"
         " strftime('%s','now'))", (doomed,)),
    )

    # Project-owned rows: a note, an understanding revision, a rule
    # attachment, the outgoing link to `keeper` and stack membership.
    sql_exec(
        ("insert into project_notes (project_id, source_task_id, body,"
         " created_at) values (?, ?, 'note body', strftime('%s','now'))",
         (doomed, first_task)),
        ("insert into project_understandings (project_id, body, commit_hash,"
         " task_id, created_at) values (?, 'understanding body', 'abc123', ?,"
         " strftime('%s','now'))", (doomed, first_task)),
        ("insert into rule_projects (rule_id, project_id, created_at)"
         " values ((select id from rules where name = 'doomed-rule'), ?,"
         " strftime('%s','now'))", (doomed,)),
        ("insert into project_links (parent_project_id, linked_project_id,"
         " relationship) values (?, ?, 'co-developed')", (doomed, keeper)),
        ("insert into stacks (name, summary, created_at)"
         " values ('doomed-stack', 's', strftime('%s','now'))",),
        ("insert into stack_projects (stack_id, project_id, relationship,"
         " created_at) values ((select id from stacks where name ="
         " 'doomed-stack'), ?, 'reference', strftime('%s','now'))", (doomed,)),
    )

    print("== the survival law: OTHER projects' linked work is NEVER taken ==")
    # `other-host` links to the doomed project (both directions of the
    # pointer graph) and owns a task + goal whose rows merely NAME the
    # doomed project through goal_links.
    sql_exec(
        ("insert into project_links (parent_project_id, linked_project_id,"
         " relationship) values (?, ?, 'reference')", (other, doomed)),
        ("insert into project_links (parent_project_id, linked_project_id,"
         " relationship) values (?, ?, 'co-developed')", (other, doomed)),
        ("insert into tasks (project_id, goal_id, kind, mode, creator, title,"
         " description, status, priority, created_at)"
         " values (?, 0, 'task', 'implement', 'human', 'Other project task',"
         " 'x', 'done', 5, strftime('%s','now'))", (other,)),
        ("insert into goals (project_id, title, brief, branch_name, status,"
         " creator, created_at)"
         " values (?, 'Other goal', 'b', 'goal/other', 'active', 'human',"
         " strftime('%s','now'))", (other,)),
    )
    other_goal = int(sql_one("select id from goals where project_id = ?", (other,)))
    other_task = int(sql_one(
        "select id from tasks where project_id = ? and kind = 'task'", (other,)))
    sql_exec(
        # other's goal links TO the doomed project (goal_links naming it).
        ("insert into goal_links (goal_id, project_id, relationship)"
         " values (?, ?, 'co-developed')", (other_goal, doomed)),
        # other's task depends on one of the doomed tasks as an
        # auto-enqueue predecessor — the reference must CLEAR, not dangle.
        ("update tasks set enqueue_after_task_id = ? where id = ?",
         (first_task, other_task)),
        # A standalone task (project 0) must never be touched.
        ("insert into tasks (project_id, goal_id, kind, mode, creator, title,"
         " description, status, priority, created_at)"
         " values (0, 0, 'task', 'implement', 'human', 'Standalone task',"
         " 'x', 'draft', 5, strftime('%s','now'))",),
        # The keeper project links to the doomed project too — its link
        # row must go, the project must stay.
        ("insert into project_links (parent_project_id, linked_project_id,"
         " relationship) values (?, ?, 'reference')", (keeper, doomed)),
        # A goal on the KEEPER project linking the doomed project: the
        # goal survives, only its link row goes.
        ("insert into goals (project_id, title, brief, branch_name, status,"
         " creator, created_at)"
         " values (?, 'Keeper goal', 'b', 'goal/keeper', 'active', 'human',"
         " strftime('%s','now'))", (keeper,)),
        ("insert into goal_links (goal_id, project_id, relationship)"
         " values ((select id from goals where project_id = ?), ?,"
         " 'reference')", (keeper, doomed)),
    )
    standalone_task = int(sql_one(
        "select id from tasks where project_id = 0 and title = 'Standalone task'"))

    print("== the page: counts, admin-only control, typed-name gate ==")
    state = slot_public(auth, "/projects/%d" % doomed, "project")
    check(state["is_admin"] is True and state.get("delete") is not None,
          "an admin's project page carries the delete action")
    check(state["delete_counts"] == "1 goal, 3 tasks",
          "the dialog counts what will go ('%s')" % state["delete_counts"])
    check(state["active_tasks"] == 0,
          "no live tasks under the doomed project (guard open)")

    plain_state = slot_public(plain, "/projects/%d" % doomed, "project")
    check(plain_state.get("delete") is None and plain_state.get("is_admin") is False,
          "a non-admin's project page hides the delete action entirely")

    # Wrong typed name refuses: nothing deleted.
    result = act(auth, state["delete"],
                 {"project_id": str(doomed), "confirm_name": "doome"})
    check("exact name" in (action_notify(result) or ""),
          "a wrong typed name refuses the deletion")
    check(sql_one("select count(*) from projects where id = ?", (doomed,)) == 1,
          "the refused confirmation left the project in place")

    print("== the guard: live tasks refuse ==")
    guard_project = make_project("guarded")
    sql_exec(
        ("insert into tasks (project_id, goal_id, kind, mode, creator, title,"
         " description, status, priority, created_at)"
         " values (?, 0, 'task', 'implement', 'human', 'Working task', 'x',"
         " 'working', 5, strftime('%s','now'))", (guard_project,)),
    )
    guard_state = slot_public(auth, "/projects/%d" % guard_project, "project")
    check(guard_state["active_tasks"] == 1,
          "the page counts the live task (guard visible up front)")
    status, body = delete_api(auth, guard_project)
    check(status == 409 and "queued, working" in body.get("error", ""),
          "the API refuses a live project with 409")
    result = act(auth, guard_state["delete"],
                 {"project_id": str(guard_project), "confirm_name": "guarded"})
    notify = action_notify(result) or ""
    check("still has 1 task" in notify or "still has 1 tasks" in notify,
          "the action refuses too, naming the live count ('%s')" % notify)
    check(sql_one("select count(*) from projects where id = ?", (guard_project,)) == 1,
          "the guarded project survived")
    # Drop the live task; now it goes.
    sql_exec(("update tasks set status = 'done' where project_id = ?", (guard_project,)))
    status, body = delete_api(auth, guard_project)
    check(status == 200 and body.get("ok") is True,
          "after the task settles, the same delete succeeds")

    print("== the delete: owned rows gone, survivors intact ==")
    status, body = delete_api(auth, doomed)
    check(status == 200 and body.get("ok") is True,
          "the doomed project deletes cleanly over the API")

    def count(query, params=()):
        return sql_one(query, params) or 0

    # Gone: the project row, its tasks, its goal and every owned child.
    check(count("select count(*) from projects where id = ?", (doomed,)) == 0,
          "the project row is gone")
    check(count("select count(*) from tasks where project_id = ?", (doomed,)) == 0,
          "every owned task is gone (goal runs included)")
    check(count("select count(*) from goals where project_id = ?", (doomed,)) == 0,
          "every owned goal is gone")
    check(count("select count(*) from task_comments where task_id in"
                " (%s)" % ",".join(str(t) for t in doomed_tasks)) == 0,
          "the owned tasks' comments are gone")
    check(count("select count(*) from task_blocks where task_id in"
                " (%s)" % ",".join(str(t) for t in doomed_tasks)) == 0,
          "the owned tasks' blocks are gone")
    check(count("select count(*) from task_rules where task_id in"
                " (%s)" % ",".join(str(t) for t in doomed_tasks)) == 0,
          "the owned tasks' rule attachments are gone")
    check(count("select count(*) from task_capabilities where task_id in"
                " (%s)" % ",".join(str(t) for t in doomed_tasks)) == 0,
          "the owned tasks' capability requirements are gone")
    check(count("select count(*) from sessions where task_id in"
                " (%s)" % ",".join(str(t) for t in doomed_tasks)) == 0,
          "the owned tasks' sessions are gone")
    check(count("select count(*) from events where session_id = ?", (session_id,)) == 0,
          "the session-carried events are gone")
    check(count("select count(*) from events where scope_type = 'task' and"
                " scope_id in (%s)" % ",".join(str(t) for t in doomed_tasks)) == 0,
          "the task-scoped events are gone")
    check(count("select count(*) from events where scope_type = 'goal' and"
                " scope_id = ?", (doomed_goal,)) == 0,
          "the goal-scoped events are gone")
    check(count("select count(*) from events where scope_type = 'project' and"
                " scope_id = ?", (doomed,)) == 0,
          "the project-scoped events are gone")
    check(count("select count(*) from goal_links where goal_id = ?", (doomed_goal,)) == 0,
          "the doomed goal's link rows are gone")
    check(count("select count(*) from project_notes where project_id = ?", (doomed,)) == 0,
          "the project's notes are gone")
    check(count("select count(*) from project_understandings where project_id = ?",
                (doomed,)) == 0,
          "the project's understanding revisions are gone")
    check(count("select count(*) from rule_projects where project_id = ?", (doomed,)) == 0,
          "the project's rule attachments are gone")
    check(count("select count(*) from project_links where parent_project_id = ?",
                (doomed,)) == 0,
          "the project's outgoing links are gone")
    check(count("select count(*) from stack_projects where project_id = ?",
                (doomed,)) == 0,
          "the project's stack memberships are gone")

    # Intact: everything NOT owned by the doomed project.
    check(count("select count(*) from projects where id = ?", (other,)) == 1
          and count("select count(*) from projects where id = ?", (keeper,)) == 1,
          "the other projects' rows survive")
    check(count("select count(*) from tasks where id = ?", (other_task,)) == 1,
          "CRITICAL: another project's task that linked to the doomed project SURVIVES")
    check(count("select count(*) from goals where id = ?", (other_goal,)) == 1,
          "CRITICAL: another project's goal that linked to the doomed project SURVIVES")
    check(count("select count(*) from tasks where id = ?", (standalone_task,)) == 1,
          "standalone tasks (project 0) are untouched")
    check(sql_one("select enqueue_after_task_id from tasks where id = ?",
                  (other_task,)) == 0,
          "the survivor's enqueue-after reference to a deleted task is CLEARED (not dangling)")
    check(count("select count(*) from project_links where parent_project_id = ?"
                " and linked_project_id = ?", (other, doomed)) == 0,
          "other project's link rows NAMING the doomed project are tidied away (pointer only)")
    check(count("select count(*) from project_links where parent_project_id = ?"
                " and linked_project_id = ?", (keeper, doomed)) == 0,
          "the keeper's link row naming the doomed project is tidied away")
    check(count("select count(*) from goal_links where project_id = ?", (doomed,)) == 0,
          "goal link rows in OTHER projects naming the doomed project are tidied (pointer only)")
    check(count("select count(*) from goal_links where goal_id = ?",
                (other_goal,)) == 0,
          "the other project's goal lost only its link row to the doomed project")
    keeper_goal = int(sql_one("select id from goals where project_id = ?", (keeper,)))
    check(count("select count(*) from goals where id = ?", (keeper_goal,)) == 1,
          "the keeper's own goal survived the pointer tidy")
    # Fleet-owned rows the cascade must never take.
    check(count("select count(*) from rules where name = 'doomed-rule'") == 1,
          "the rule DEFINITIONS survive (only attachments went)")
    check(count("select count(*) from capabilities where name = 'doomed-cap'") == 1,
          "the capability DEFINITIONS survive (only requirements went)")
    check(count("select count(*) from stacks where name = 'doomed-stack'") == 1,
          "the stack itself survives (only its membership row went)")
    check(count("select count(*) from saddles where name = 'doomed-saddle'") == 1,
          "the saddle registration survives")

    print("== the page after the delete ==")
    # The detail page of a deleted project reports not-found.
    state = slot_public(auth, "/projects/%d" % doomed, "project")
    check(state["exists"] is False,
          "the deleted project's page reports not found")
    names = [p["name"] for p in slot_public(auth, "/projects", "projects")["projects"]]
    check("doomed" not in names and "keeper" in names,
          "the projects list shows the survivors only")

    print("== the UI path: the action deletes and redirects ==")
    # End-to-end through the page action (the dialog's form): a fresh
    # project with one goal and one task goes, the reply navigates to
    # /projects with a success notify.
    ui_doomed = make_project("ui-doomed")
    sql_exec(
        ("insert into tasks (project_id, goal_id, kind, mode, creator, title,"
         " description, status, priority, created_at)"
         " values (?, 0, 'task', 'implement', 'human', 'UI task', 'x',"
         " 'done', 5, strftime('%s','now'))", (ui_doomed,)),
        ("insert into goals (project_id, title, brief, branch_name, status,"
         " creator, created_at)"
         " values (?, 'UI goal', 'b', 'goal/ui', 'complete', 'human',"
         " strftime('%s','now'))", (ui_doomed,)),
    )
    ui_state = slot_public(auth, "/projects/%d" % ui_doomed, "project")
    check(ui_state["delete_counts"] == "1 goal, 1 task",
          "the UI project's counts read '1 goal, 1 task' ('%s')"
          % ui_state["delete_counts"])
    result = act(auth, ui_state["delete"],
                 {"project_id": str(ui_doomed), "confirm_name": "ui-doomed"})
    notify = action_notify(result) or ""
    check("deleted" in notify and "untouched" in notify,
          "the action reports the delete and the survival guarantee ('%s')" % notify)
    check(any(d.get("type") == "navigate" and d.get("uri") == "/projects"
              for d in result), "the action redirects to the projects list")
    check(count("select count(*) from projects where id = ?", (ui_doomed,)) == 0
          and count("select count(*) from tasks where project_id = ?", (ui_doomed,)) == 0
          and count("select count(*) from goals where project_id = ?", (ui_doomed,)) == 0,
          "the UI delete took the project with its task and goal")

    # Idempotence of the wire: deleting the same project again is a 404.
    status, body = delete_api(auth, doomed)
    check(status == 404, "deleting an already-deleted project answers 404")

    # Housekeeping: the leftover helper rows this bundle created.
    sql_exec(
        ("delete from goal_links where goal_id in (select id from goals where"
         " project_id in (?, ?))", (keeper, standalone_host)),
        ("delete from goals where project_id in (?, ?)", (keeper, standalone_host)),
        ("delete from tasks where id = ?", (standalone_task,)),
        ("delete from tasks where project_id = ?", (other,)),
        ("delete from goal_links where goal_id = ?", (other_goal,)),
        ("delete from goals where id = ?", (other_goal,)),
        ("delete from project_links where parent_project_id in (?, ?)",
         (other, keeper)),
        ("delete from stack_projects where stack_id ="
          " (select id from stacks where name = 'doomed-stack')",),
        ("delete from stacks where name = 'doomed-stack'",),
        ("delete from rules where name = 'doomed-rule'",),
        ("delete from capabilities where name = 'doomed-cap'",),
        ("delete from saddles where name = 'doomed-saddle'",),
        ("delete from projects where id in (?, ?, ?)", (other, keeper,
                                                         standalone_host)),
    )