"""Split the reward letter per severity tier, and state the money in it. The single ``reward_confirmed`` template was one historical reply frozen into a template, and it froze that reply's *verdict* with it: "this issue affects a low-impact business area" "the affected website is open source and publicly accessible" "we consider this issue to be relatively low in business severity" Pre-selected for every ``confirmed`` report, so a researcher who filed a critical finding would be told it was low-impact. Operators worked around it by rewriting the letter by hand, and the amount — the one thing the researcher is actually waiting to hear — went missing in the rewrite. That is the reported symptom: "回复的邮件没有体现钱". It also asked for ``bank account information``, which contradicts the standing decision that Helix stores no banking data: the letter solicited by email exactly what we deliberately refuse to hold. This migration: * deletes ``reward_confirmed`` — the row AND the catalogue entry (removed in the same PR). Deleting only the row would leave the config page's "restore builtin template" able to resurrect it. * inserts five tier letters (``reward_critical`` … ``reward_informational``) that state ``USD {amount_usd}``, assert nothing about impact beyond the tier itself, and ask for an invoice while saying finance will arrange the payment method separately. * inserts ``payment_sent`` so "we have paid you" stops being freehand. History is unaffected: ``last_reply_template_key`` is a plain ``String(64)`` with no foreign key, and event meta carries the key as JSON, so past replies keep their key as text. The only loss is that the picker cannot resolve the retired key to a display name on old rows. Idempotent: keyed on ``key``, so a re-run inserts nothing. Reversible: downgrade restores the old row verbatim and drops the new ones. Revision ID: 545 Revises: 544 """ from __future__ import annotations import uuid import sqlalchemy as sa from alembic import op revision = "545" down_revision = "544" branch_labels = None depends_on = None TABLE = "bounty_reply_templates" RETIRED_KEY = "reward_confirmed" _SUBJECT = "Re: your security report to PingCAP [{report_code}]" _CORE_DOMAINS = "tidbcloud.com, tidb-cloud.com, tidbapi.com" _REWARD_BODY = """Hi {researcher_name}, Thank you for reporting this to us, and for the detail you provided. We have reproduced and confirmed the issue you reported in \ {affected_system}. We have graded it **{severity_label}** under our \ published severity ladder. Your reward for this report is **USD {amount_usd}**. What we need from you to pay it: 1. An invoice addressed to the entity below. 2. Nothing else for now — our finance team will contact you directly to arrange the payment method. Please do not send bank details by email. {entity_block} We will confirm here once the payment has been issued. We welcome further review of """ + _CORE_DOMAINS + """ and other \ TiDB-related products; reports there may qualify for a higher tier. Thank you again for reporting this responsibly. Best regards, {signer_name} {signer_title} {company_name}""" _PAID_BODY = """Hi {researcher_name}, The reward of USD {amount_usd} for {report_code} has been issued. If it has not reached you within ten business days, reply to this thread \ and we will chase it with our finance team. Thank you again for reporting this to us responsibly. Best regards, {signer_name} {signer_title} {company_name}""" _REWARD_VARS = [ "researcher_name", "report_code", "affected_system", "severity_label", "amount_usd", "entity_block", "signer_name", "signer_title", "company_name", ] _PAID_VARS = [ "researcher_name", "report_code", "amount_usd", "signer_name", "signer_title", "company_name", ] # (key, name_zh, name_en, name_ja, sort_order) _TIERS = [ ("reward_critical", "确认有效 · 严重档发奖(报金额)", "Valid — Critical tier, reward stated", "有効 · 重大ランクの報奨金(金額明記)", 61), ("reward_high", "确认有效 · 高危档发奖(报金额)", "Valid — High tier, reward stated", "有効 · 高ランクの報奨金(金額明記)", 62), ("reward_medium", "确认有效 · 中危档发奖(报金额)", "Valid — Medium tier, reward stated", "有効 · 中ランクの報奨金(金額明記)", 63), ("reward_low", "确认有效 · 低危档发奖(报金额)", "Valid — Low tier, reward stated", "有効 · 低ランクの報奨金(金額明記)", 64), ("reward_informational", "确认有效 · 告知档发奖(报金额)", "Valid — Informational tier, reward stated", "有効 · 参考情報ランクの報奨金(金額明記)", 65), ] _OLD_BODY = """Hi, Thank you for reporting the issue to us. After investigation and confirmation, we have verified that the issue \ you reported does exist. Based on our assessment, this issue affects a \ low-impact business area and does not involve sensitive business logic \ or user data. As the affected website is open source and publicly accessible, we \ consider this issue to be relatively low in business severity. As a token of our appreciation for your valid report, we would like to \ offer you a reward of USD {amount_usd}. Please provide your bank account \ information so that we can proceed with the payment. Thank you. Our finance staff requested an invoice. Could you provide one? {entity_block} Best regards, {signer_name} {signer_title} {company_name}""" def _table() -> sa.Table: meta = sa.MetaData() return sa.Table( TABLE, meta, sa.Column("id", sa.Uuid()), sa.Column("key", sa.String(64)), sa.Column("name_zh", sa.String(128)), sa.Column("name_en", sa.String(128)), sa.Column("name_ja", sa.String(128)), sa.Column("category", sa.String(24)), sa.Column("subject", sa.String(500)), sa.Column("body", sa.Text()), sa.Column("variables", sa.JSON()), sa.Column("sort_order", sa.Integer()), sa.Column("enabled", sa.Boolean()), sa.Column("is_builtin", sa.Boolean()), sa.Column("use_count", sa.Integer()), ) def _existing_keys(conn: sa.engine.Connection) -> set[str]: rows = conn.execute( sa.text(f"SELECT `key` FROM {TABLE}"), # noqa: S608 - constant ).fetchall() return {r[0] for r in rows} def upgrade() -> None: conn = op.get_bind() # The seeder only fires on an empty table, so on any live database # these rows have to be inserted explicitly or they never appear. if not sa.inspect(conn).has_table(TABLE): return table = _table() present = _existing_keys(conn) rows = [] for key, name_zh, name_en, name_ja, order in _TIERS: if key in present: continue rows.append({ "id": uuid.uuid4(), "key": key, "name_zh": name_zh, "name_en": name_en, "name_ja": name_ja, "category": "reward", "subject": _SUBJECT, "body": _REWARD_BODY, "variables": _REWARD_VARS, "sort_order": order, "enabled": True, "is_builtin": True, "use_count": 0, }) if "payment_sent" not in present: rows.append({ "id": uuid.uuid4(), "key": "payment_sent", "name_zh": "已付款通知", "name_en": "Payment issued", "name_ja": "支払い完了の通知", "category": "reward", "subject": ( "Re: your security report to PingCAP [{report_code}]" " — payment issued" ), "body": _PAID_BODY, "variables": _PAID_VARS, "sort_order": 70, "enabled": True, "is_builtin": True, "use_count": 0, }) if rows: op.bulk_insert(table, rows) # Retire the frozen-verdict letter. Safe: no FK points at this table, # and reports keep the key as text in ``last_reply_template_key``. conn.execute( sa.text(f"DELETE FROM {TABLE} WHERE `key` = :k"), # noqa: S608 {"k": RETIRED_KEY}, ) def downgrade() -> None: conn = op.get_bind() if not sa.inspect(conn).has_table(TABLE): return keys = [key for key, *_ in _TIERS] + ["payment_sent"] conn.execute( sa.text( # noqa: S608 - table name is a module constant f"DELETE FROM {TABLE} WHERE `key` IN :keys", ).bindparams(sa.bindparam("keys", expanding=True)), {"keys": keys}, ) if RETIRED_KEY not in _existing_keys(conn): op.bulk_insert(_table(), [{ "id": uuid.uuid4(), "key": RETIRED_KEY, "name_zh": "确认有效·发奖并索取收款信息/发票", "name_en": "Valid — reward offered, request payee details + invoice", "name_ja": "有効・報奨金と請求書の依頼", "category": "reward", "subject": _SUBJECT, "body": _OLD_BODY, "variables": [ "report_code", "amount_usd", "entity_block", "signer_name", "signer_title", "company_name", ], "sort_order": 60, "enabled": True, "is_builtin": True, "use_count": 0, }])