# Template: Spam signup monitoring

This task will query a database to get all users who were created in the last 24 hours.
If the user's email is from a suspcicious domain, the task will ask the user to confirm and suspend the users.

## Next steps
- Replace `isSuspiciousEmail` with your own logic for detecting suspicious users.
- Navigate to the spam_signup_monitoring directory: `cd spam_signup_monitoring`
- Develop your template locally: `airplane dev`
- Deploy your tasks and view: `airplane deploy --yes`
- Visit the docs to learn more about how to build views: https://docs.airplane.dev/views/getting-started

import airplane


@airplane.task(
    name="Spam Signup Monitoring",
    resources=[airplane.Resource("demo_db")],
    schedules=[
        airplane.Schedule(
            slug="spam_monitoring_noon_daily",
            cron="0 12 * * *",
        ),
    ],
    timeout=3600,
)
def spam_signup_monitoring():
    # Find new users created in the last 24 hours.
    run = airplane.sql.query(
        "demo_db",
        """
        SELECT id, email, name, is_suspended
        FROM users 
        WHERE created_at > now() - interval '24 hour';
        """,
    )
    new_users = run.output["Q1"]
    if len(new_users) == 0:
        print("No new users signed up in the last 24 hours.")
        return
    suspicious_users = [user for user in new_users if isSuspiciousEmail(user["email"])]
    for user in new_users:
        user["is_suspicious"] = isSuspiciousEmail(user["email"])

    airplane.display.table(
        sorted(new_users, key=lambda user: user["is_suspicious"] == False)
    )

    airplane.display.text(
        "Found {} suspicious users: {}".format(
            len(suspicious_users), [user["email"] for user in suspicious_users]
        )
    )
    if len(suspicious_users) == 0:
        print("No suspicious users found.")
        return

    airplane.prompt(
        description=(
            "Review the suspicious users above. If it looks correct, click OK to suspend users."
        ),
        reviewers=airplane.PromptReviewers(
            groups=["admins"],
        ),
    )
    suspended = suspendUsers([user["id"] for user in suspicious_users])

    return {"suspended_users": suspended}


banned_domains = ["suspicious_domain.com", "suspicious_domain2.com"]


# TODO: Replace with your logic.
def isSuspiciousEmail(email: str):
    return any([email.split("@")[1] in banned_domain for banned_domain in banned_domains])

def suspendUsers(user_ids: list[str]):
    print("Suspending users: {}".format(user_ids))
    user_id_string = ",".join(user_ids)
    run = airplane.sql.query(
        sql_resource="demo_db",
        query="""
        UPDATE users
        SET is_suspended = true
        WHERE id = ANY(string_to_array(:user_ids, ',')::text[]) 
        RETURNING id, email, name;
        """,
        query_args={"user_ids": user_id_string},
    )
    return run.output.get("Q1")


Task

Pairs well with

Spam signup monitoring

Task

Pairs well with