"""The answer library gains an employee-facing face. The 答案库 was built for one reader: the person filling in a customer's security questionnaire. Everything in it — "是否管控 USB 存储、外设与打印 等本地外发通道?", "员工使用生成式 AI 工具时公司数据如何保护?" — is also the answer to a question our own staff ask, and had no way to look up. They asked IT in a群, or guessed. A knowledge base that already holds the answer and only shows it to seven people is a shelf, not a platform. Three schema moves make the employee face possible. ``employee_visible`` decides what an employee may read. It defaults to TRUE and every existing row inherits that default, which is the approved call (the library carries no customer-identifying content) — but the column exists so that the security team has a switch rather than a deployment. ``is_featured`` is the other half of curation: the handful of questions that deserve to sit above the search box instead of waiting to be searched for. The three counters are the read side of the same loop. ``usage_count`` already means "times reused in a customer questionnaire" and must keep meaning exactly that, so employee reads get their own column rather than inflating a number the security team uses to judge which answers earn a review. Helpful / unhelpful are the only signal that an answer is technically correct and practically useless. ``auditor_security_faq_gaps`` is what makes the library compound. When an employee asks something the library cannot answer, that question is worth more than the ones it can — it is the next entry, named by the person who needed it. Rows are keyed by a normalised query so thirty people asking the same thing produce one ledger line with ``ask_count = 30`` rather than thirty lines of noise. An admin's decision (answered / dismissed) is never overwritten by a later ask: the upsert bumps the counters and leaves the whole resolution family — status, resolved_entry_id, resolved_by_person_id, resolved_at, note — untouched, so a dismissed gap stays dismissed. What it does record is ``last_asked_at``, which lets the console show the one case that matters more than either: a gap marked answered that people keep asking anyway. ``auditor_security_faq_votes`` exists so the helpful counters are honest. One row per (entry, person), so a vote can be changed but not stacked, and the denormalised counters on the entry stay correct because they move in the same transaction as the vote row. Revision ID: 560 Revises: 559 """ from __future__ import annotations import sqlalchemy as sa from alembic import op from app.core.migration_helpers import id_type_for, table_kw_for revision = "560" down_revision = "559" branch_labels = None depends_on = None ENTRIES = "auditor_answer_library_entries" GAPS = "auditor_security_faq_gaps" VOTES = "auditor_security_faq_votes" # (column, type, server_default) — added in order, guarded individually so # a partially-applied run is resumable. _ENTRY_COLUMNS: tuple[tuple[str, sa.types.TypeEngine, str], ...] = ( ("employee_visible", sa.Boolean(), "1"), ("is_featured", sa.Boolean(), "0"), ("employee_view_count", sa.Integer(), "0"), ("employee_helpful_count", sa.Integer(), "0"), ("employee_unhelpful_count", sa.Integer(), "0"), ) _EMPLOYEE_INDEX = "idx_auditor_answer_lib_employee" def _column_exists(bind, table: str, column: str) -> bool: return bind.execute( sa.text( "SELECT 1 FROM information_schema.columns " "WHERE table_schema = DATABASE() AND table_name = :t " "AND column_name = :c" ), {"t": table, "c": column}, ).scalar() is not None def _index_exists(bind, table: str, index: str) -> bool: return bind.execute( sa.text( "SELECT 1 FROM information_schema.statistics " "WHERE table_schema = DATABASE() AND table_name = :t " "AND index_name = :i LIMIT 1" ), {"t": table, "i": index}, ).scalar() is not None def upgrade() -> None: bind = op.get_bind() # ── 1. the employee face of an existing entry ───────────────── for column, type_, default in _ENTRY_COLUMNS: if not _column_exists(bind, ENTRIES, column): op.add_column( ENTRIES, sa.Column( column, type_, nullable=False, server_default=sa.text(default), ), ) # The employee list always filters on visible + active and usually on # category, in that order of selectivity. if not _index_exists(bind, ENTRIES, _EMPLOYEE_INDEX): op.create_index( _EMPLOYEE_INDEX, ENTRIES, ["employee_visible", "status", "category"], ) # ── 2. the gap ledger ───────────────────────────────────────── # No FK on person / entry columns: TiDB takes a lock on the parent row # for every child INSERT, and the ask path is the hot one — an # employee typing in a search box must never queue behind a lock on # ``identity_persons``. if not sa.inspect(bind).has_table(GAPS): op.create_table( GAPS, sa.Column("id", id_type_for("identity_persons"), primary_key=True), # Lower-cased, whitespace-collapsed, truncated. The dedup key. sa.Column("query_norm", sa.String(255), nullable=False), # What the most recent asker actually typed, verbatim. sa.Column("query_sample", sa.Text(), nullable=False), sa.Column("lang", sa.String(8), nullable=False, server_default="zh"), sa.Column( "ask_count", sa.Integer(), nullable=False, server_default="1", ), sa.Column("first_asked_at", sa.DateTime(timezone=True), nullable=False), sa.Column("last_asked_at", sa.DateTime(timezone=True), nullable=False), sa.Column( "last_asker_person_id", id_type_for("identity_persons"), nullable=True, ), # open | answered | dismissed sa.Column( "status", sa.String(16), nullable=False, server_default="open", ), sa.Column( "resolved_entry_id", id_type_for("identity_persons"), nullable=True, ), sa.Column( "resolved_by_person_id", id_type_for("identity_persons"), nullable=True, ), sa.Column("resolved_at", sa.DateTime(timezone=True), nullable=True), sa.Column("note", sa.String(500), nullable=True), sa.Column( "created_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.text("CURRENT_TIMESTAMP"), ), sa.Column( "updated_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.text("CURRENT_TIMESTAMP"), onupdate=sa.text("CURRENT_TIMESTAMP"), ), **table_kw_for("identity_persons"), ) # The upsert depends on this being unique — it is the ON # DUPLICATE KEY target, not merely a lookup index. op.create_index( "uq_auditor_security_faq_gaps_query", GAPS, ["query_norm"], unique=True, ) op.create_index( "ix_auditor_security_faq_gaps_status", GAPS, ["status", "ask_count"], ) # ── 3. one vote per person per entry ────────────────────────── if not sa.inspect(bind).has_table(VOTES): op.create_table( VOTES, sa.Column("id", id_type_for("identity_persons"), primary_key=True), sa.Column( "entry_id", id_type_for("identity_persons"), nullable=False, ), sa.Column( "person_id", id_type_for("identity_persons"), nullable=False, ), sa.Column("helpful", sa.Boolean(), nullable=False), sa.Column( "created_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.text("CURRENT_TIMESTAMP"), ), sa.Column( "updated_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.text("CURRENT_TIMESTAMP"), onupdate=sa.text("CURRENT_TIMESTAMP"), ), **table_kw_for("identity_persons"), ) op.create_index( "uq_auditor_security_faq_votes", VOTES, ["entry_id", "person_id"], unique=True, ) def downgrade() -> None: bind = op.get_bind() if sa.inspect(bind).has_table(VOTES): op.drop_table(VOTES) if sa.inspect(bind).has_table(GAPS): op.drop_table(GAPS) if _index_exists(bind, ENTRIES, _EMPLOYEE_INDEX): op.drop_index(_EMPLOYEE_INDEX, table_name=ENTRIES) for column, _type, _default in reversed(_ENTRY_COLUMNS): if _column_exists(bind, ENTRIES, column): op.drop_column(ENTRIES, column)