"""External white-hat bug bounty operations (5 tables + seeds). Creates the bounty domain: researchers, reports, the append-only report event log, the configurable severity/reward ladder and the English reply template library. Seeds the ladder + templates from ``app.domains.bounty.catalog`` (single source of truth, also used by the console's "restore default" action) and the budget / signature platform settings. Revision ID: 533 Revises: 532 """ from __future__ import annotations import json import uuid import sqlalchemy as sa from alembic import op from app.core.migration_helpers import id_type_for, table_kw_for from app.domains.bounty import constants as const from app.domains.bounty.catalog import ( DEFAULT_SIGNATURE, REPLY_TEMPLATES, SEVERITY_TIERS, ) revision = "533" down_revision = "532" branch_labels = None depends_on = None RESEARCHERS = "bounty_researchers" REPORTS = "bounty_reports" EVENTS = "bounty_report_events" TIERS = "bounty_severity_tiers" TEMPLATES = "bounty_reply_templates" _ALL_TABLES = (EVENTS, REPORTS, TEMPLATES, TIERS, RESEARCHERS) def _table_exists(bind, name: str) -> bool: return bind.execute( sa.text( "SELECT 1 FROM information_schema.tables " "WHERE table_schema = DATABASE() AND table_name = :n" ), {"n": name}, ).scalar() is not None def upgrade() -> None: bind = op.get_bind() # Every FK in this migration points at ``identity_persons`` (plus # intra-file parents), so one table charset satisfies all of them — # see the mixed-parent trap in the TiDB FK checklist. pid = id_type_for("identity_persons") tkw = table_kw_for("identity_persons") # ── 1. Researchers ────────────────────────────────────────────── if not _table_exists(bind, RESEARCHERS): op.create_table( RESEARCHERS, sa.Column("id", pid, nullable=False), sa.Column("email", sa.String(320), nullable=False), sa.Column("display_name", sa.String(255)), sa.Column("handle", sa.String(128)), sa.Column("country", sa.String(64)), sa.Column("notes", sa.Text), sa.Column("is_blocked", sa.Boolean, nullable=False, server_default="0"), sa.Column("first_report_at", sa.DateTime(timezone=True)), sa.Column("last_report_at", sa.DateTime(timezone=True)), sa.Column("created_by", pid), sa.Column("created_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.func.now()), sa.Column("updated_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.func.now()), sa.PrimaryKeyConstraint("id"), sa.UniqueConstraint("email", name="uq_bounty_researchers_email"), sa.ForeignKeyConstraint(["created_by"], ["identity_persons.id"], ondelete="SET NULL"), **tkw, ) op.create_index("ix_bounty_researchers_last_report", RESEARCHERS, ["last_report_at"]) # ── 2. Reports ────────────────────────────────────────────────── if not _table_exists(bind, REPORTS): op.create_table( REPORTS, sa.Column("id", pid, nullable=False), sa.Column("code", sa.String(32), nullable=False), sa.Column("title", sa.String(const.MAX_TITLE_CHARS), nullable=False), sa.Column("researcher_id", pid, nullable=False), sa.Column("source", sa.String(16), nullable=False, server_default=const.SOURCE_EMAIL), sa.Column("reported_at", sa.DateTime(timezone=True), nullable=False), sa.Column("raw_report", sa.Text), sa.Column("summary", sa.Text), sa.Column("reproduction", sa.Text), sa.Column("affected_system", sa.String(255)), sa.Column("affected_urls", sa.JSON), sa.Column("vuln_type", sa.String(32)), sa.Column("cve_id", sa.String(64)), sa.Column("severity", sa.String(24)), sa.Column("suggested_severity", sa.String(24)), sa.Column("status", sa.String(24), nullable=False, server_default=const.STATUS_NEW), sa.Column("remediation_status", sa.String(24), nullable=False, server_default=const.REMEDIATION_NOT_APPLICABLE), sa.Column("payment_status", sa.String(24), nullable=False, server_default=const.PAYMENT_NOT_APPLICABLE), sa.Column("assignee_person_id", pid), sa.Column("assignee_name", sa.String(255)), sa.Column("assignee_department", sa.String(255)), sa.Column("assigned_at", sa.DateTime(timezone=True)), sa.Column("assigned_by", pid), sa.Column("last_nudged_at", sa.DateTime(timezone=True)), sa.Column("owner_decision", sa.String(24)), sa.Column("owner_note", sa.Text), sa.Column("owner_responded_at", sa.DateTime(timezone=True)), sa.Column("fix_eta", sa.Date), sa.Column("fix_due_date", sa.Date), sa.Column("fixed_at", sa.DateTime(timezone=True)), sa.Column("reward_amount_usd", sa.Numeric(12, 2)), sa.Column("reward_is_override", sa.Boolean, nullable=False, server_default="0"), sa.Column("reward_decided_at", sa.DateTime(timezone=True)), sa.Column("fiscal_year", sa.Integer), sa.Column("payment_requested_at", sa.DateTime(timezone=True)), sa.Column("paid_at", sa.DateTime(timezone=True)), sa.Column("payment_reference", sa.String(128)), sa.Column("payment_note", sa.Text), sa.Column("last_replied_at", sa.DateTime(timezone=True)), sa.Column("last_reply_template_key", sa.String(64)), sa.Column("reply_count", sa.Integer, nullable=False, server_default="0"), sa.Column("closed_at", sa.DateTime(timezone=True)), sa.Column("created_by", pid), sa.Column("created_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.func.now()), sa.Column("updated_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.func.now()), sa.Column("deleted_at", sa.DateTime(timezone=True)), sa.PrimaryKeyConstraint("id"), sa.UniqueConstraint("code", name="uq_bounty_reports_code"), sa.ForeignKeyConstraint(["researcher_id"], [f"{RESEARCHERS}.id"]), sa.ForeignKeyConstraint(["assignee_person_id"], ["identity_persons.id"], ondelete="SET NULL"), sa.ForeignKeyConstraint(["assigned_by"], ["identity_persons.id"], ondelete="SET NULL"), sa.ForeignKeyConstraint(["created_by"], ["identity_persons.id"], ondelete="SET NULL"), **tkw, ) op.create_index("ix_bounty_reports_status", REPORTS, ["status", "deleted_at"]) op.create_index("ix_bounty_reports_assignee", REPORTS, ["assignee_person_id", "status"]) op.create_index("ix_bounty_reports_researcher", REPORTS, ["researcher_id", "reported_at"]) op.create_index("ix_bounty_reports_budget", REPORTS, ["fiscal_year", "payment_status"]) op.create_index("ix_bounty_reports_severity", REPORTS, ["severity", "status"]) op.create_index("ix_bounty_reports_reported", REPORTS, ["reported_at"]) op.create_index("ix_bounty_reports_remediation", REPORTS, ["remediation_status", "fix_due_date"]) # ── 3. Report events ──────────────────────────────────────────── if not _table_exists(bind, EVENTS): op.create_table( EVENTS, sa.Column("id", pid, nullable=False), sa.Column("report_id", pid, nullable=False), sa.Column("event_type", sa.String(32), nullable=False), sa.Column("actor_kind", sa.String(16), nullable=False, server_default=const.ACTOR_USER), sa.Column("actor_person_id", pid), sa.Column("actor_name", sa.String(255)), sa.Column("visibility", sa.String(16), nullable=False, server_default=const.VISIBILITY_INTERNAL), sa.Column("body", sa.Text), sa.Column("meta", sa.JSON), sa.Column("created_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.func.now()), sa.PrimaryKeyConstraint("id"), sa.ForeignKeyConstraint(["report_id"], [f"{REPORTS}.id"], ondelete="CASCADE"), sa.ForeignKeyConstraint(["actor_person_id"], ["identity_persons.id"], ondelete="SET NULL"), **tkw, ) op.create_index("ix_bounty_events_report", EVENTS, ["report_id", "created_at"]) op.create_index("ix_bounty_events_type", EVENTS, ["event_type", "created_at"]) # ── 4. Severity tiers ─────────────────────────────────────────── if not _table_exists(bind, TIERS): op.create_table( TIERS, sa.Column("id", pid, nullable=False), sa.Column("key", sa.String(24), nullable=False), sa.Column("label_zh", sa.String(64), nullable=False), sa.Column("label_en", sa.String(64), nullable=False), sa.Column("label_ja", sa.String(64), nullable=False), sa.Column("default_reward_usd", sa.Numeric(12, 2), nullable=False, server_default="0"), sa.Column("max_reward_usd", sa.Numeric(12, 2)), sa.Column("fix_sla_days", sa.Integer), sa.Column("color", sa.String(16)), sa.Column("sort_order", sa.Integer, nullable=False, server_default="100"), sa.Column("enabled", sa.Boolean, nullable=False, server_default="1"), sa.Column("is_builtin", sa.Boolean, nullable=False, server_default="0"), sa.Column("updated_by", pid), sa.Column("created_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.func.now()), sa.Column("updated_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.func.now()), sa.PrimaryKeyConstraint("id"), sa.UniqueConstraint("key", name="uq_bounty_severity_tiers_key"), sa.ForeignKeyConstraint(["updated_by"], ["identity_persons.id"], ondelete="SET NULL"), **tkw, ) op.create_index("ix_bounty_severity_tiers_order", TIERS, ["sort_order"]) # ── 5. Reply templates ────────────────────────────────────────── if not _table_exists(bind, TEMPLATES): op.create_table( TEMPLATES, sa.Column("id", pid, nullable=False), sa.Column("key", sa.String(64), nullable=False), sa.Column("name_zh", sa.String(128), nullable=False), sa.Column("name_en", sa.String(128), nullable=False), sa.Column("name_ja", sa.String(128), nullable=False), sa.Column("category", sa.String(24), nullable=False), sa.Column("subject", sa.String(500), nullable=False), sa.Column("body", sa.Text, nullable=False), sa.Column("variables", sa.JSON), sa.Column("sort_order", sa.Integer, nullable=False, server_default="100"), sa.Column("enabled", sa.Boolean, nullable=False, server_default="1"), sa.Column("is_builtin", sa.Boolean, nullable=False, server_default="0"), sa.Column("use_count", sa.Integer, nullable=False, server_default="0"), sa.Column("updated_by", pid), sa.Column("created_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.func.now()), sa.Column("updated_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.func.now()), sa.PrimaryKeyConstraint("id"), sa.UniqueConstraint("key", name="uq_bounty_reply_templates_key"), sa.ForeignKeyConstraint(["updated_by"], ["identity_persons.id"], ondelete="SET NULL"), **tkw, ) op.create_index("ix_bounty_reply_templates_order", TEMPLATES, ["category", "sort_order"]) _seed_tiers(bind) _seed_templates(bind) _seed_settings(bind) def _seed_tiers(bind) -> None: """Insert any missing builtin tier. Existing rows are left alone — the operator's edited reward amounts must survive a re-run.""" existing = { r[0] for r in bind.execute(sa.text(f"SELECT `key` FROM {TIERS}")).all() } rows = [ { "id": str(uuid.uuid4()), "key": t["key"], "label_zh": t["label_zh"], "label_en": t["label_en"], "label_ja": t["label_ja"], "default_reward_usd": t["default_reward_usd"], "max_reward_usd": t["max_reward_usd"], "fix_sla_days": t["fix_sla_days"], "color": t["color"], "sort_order": t["sort_order"], } for t in SEVERITY_TIERS if t["key"] not in existing ] for row in rows: bind.execute( sa.text( f"INSERT INTO {TIERS} " "(id, `key`, label_zh, label_en, label_ja, default_reward_usd, " " max_reward_usd, fix_sla_days, color, sort_order, enabled, " " is_builtin, created_at, updated_at) " "VALUES (:id, :key, :label_zh, :label_en, :label_ja, " " :default_reward_usd, :max_reward_usd, :fix_sla_days, :color, " " :sort_order, 1, 1, UTC_TIMESTAMP(), UTC_TIMESTAMP())" ), row, ) def _seed_templates(bind) -> None: """Insert any missing builtin template, preserving operator edits.""" existing = { r[0] for r in bind.execute(sa.text(f"SELECT `key` FROM {TEMPLATES}")).all() } for t in REPLY_TEMPLATES: if t["key"] in existing: continue bind.execute( sa.text( f"INSERT INTO {TEMPLATES} " "(id, `key`, name_zh, name_en, name_ja, category, subject, body, " " variables, sort_order, enabled, is_builtin, use_count, " " created_at, updated_at) " "VALUES (:id, :key, :name_zh, :name_en, :name_ja, :category, " " :subject, :body, :variables, :sort_order, 1, 1, 0, " " UTC_TIMESTAMP(), UTC_TIMESTAMP())" ), { "id": str(uuid.uuid4()), "key": t["key"], "name_zh": t["name_zh"], "name_en": t["name_en"], "name_ja": t["name_ja"], "category": t["category"], "subject": t["subject"], "body": t["body"], "variables": json.dumps(t["variables"]), "sort_order": t["sort_order"], }, ) def _seed_settings(bind) -> None: """Seed budget + reply-signature settings if absent.""" for key, value, desc in ( ( const.SETTING_BUDGET, { "default_usd": const.DEFAULT_BUDGET_USD, "by_fy": {}, "currency": const.DEFAULT_CURRENCY, }, "漏洞赏金财年预算(USD)。by_fy 可按财年覆盖 default_usd。", ), ( const.SETTING_SIGNATURE, DEFAULT_SIGNATURE, "白帽子回信的签名与法人主体信息,渲染邮件模板时自动带入。", ), ): exists = bind.execute( sa.text("SELECT 1 FROM platform_settings WHERE `key` = :k LIMIT 1"), {"k": key}, ).scalar() if exists: continue bind.execute( sa.text( "INSERT INTO platform_settings (`key`, `value`, description, " " created_at, updated_at) " "VALUES (:k, :v, :d, UTC_TIMESTAMP(), UTC_TIMESTAMP())" ), {"k": key, "v": json.dumps(value), "d": desc}, ) def downgrade() -> None: bind = op.get_bind() for key in (const.SETTING_BUDGET, const.SETTING_SIGNATURE): bind.execute( sa.text("DELETE FROM platform_settings WHERE `key` = :k"), {"k": key}, ) # Children before parents: events → reports → researchers. for name in _ALL_TABLES: if _table_exists(bind, name): op.drop_table(name)