# WP12 Security, Audit, and Runtime Evidence Implementation Plan

> **For agentic workers:** REQUIRED SUB-SKILL: Use superpowers:subagent-driven-development (recommended) or superpowers:executing-plans to implement this plan task-by-task. Steps use checkbox (`- [ ]`) syntax for tracking.

**Goal:** Consolidate the phase-one device-governance security controls into an admin-only audit center that proves credential protection, shows six categories of key operational evidence, and detects later evidence tampering through signed seals.

**Architecture:** Existing source tables remain the authoritative operational records. A read-only repository normalizes safe fields from login, ingestion, entity resolution, publication, remediation, and knowledge-query records; a domain service builds deterministic canonical hashes and HMAC-SHA256 seals without copying raw questions, credentials, evidence payloads, IP addresses, user agents, or free-form error text. A system-management API and Vue page expose security checks, coverage, bounded audit events, seal creation, and later verification.

**Tech Stack:** Flask, SQLAlchemy text queries, PostgreSQL/Alembic, Python `hashlib`/`hmac`, Vue 2 + Vuetify, Node model tests, pytest.

## Global Constraints

- Work on `codex/dataops-phase1-equipment-governance`; do not push or deploy to production.
- Run only WP12 and directly affected tests; do not run the full regression suite.
- `app/` is authoritative and changed backend files must be copied byte-for-byte to `deployment/app/`.
- Audit APIs are admin-only and must never return credentials, raw knowledge questions, IP addresses, user agents, free-form failure details, remediation notes, evidence payloads, or source configuration.
- “Tamper protection” means signed tamper-evident sealing and later verification; it does not claim to prevent a database administrator from changing rows.
- WP13 retains offline packaging, database backup, restore, migration rehearsal, and deployment rollback.

---

### Task 1: Canonical Audit Events and Signed Evidence Seals

**Files:**
- Create: `app/core/system/governance_audit.py`
- Test: `tests/system/test_governance_audit.py`

**Interfaces:**
- Consumes: normalized dictionaries with `event_uid`, `category`, `action`, `status`, `actor_uid`, `resource_type`, `resource_uid`, `occurred_at`, and `safe_detail`.
- Produces: `GovernanceAuditService.list_events(...)`, `GovernanceAuditService.coverage(...)`, `GovernanceAuditService.create_seal(...)`, and `GovernanceAuditService.verify_seal(...)`.

- [x] **Step 1: Write failing domain tests**

  Cover deterministic ordering and hashing, secret-like key rejection/redaction, six required coverage categories, bounded pagination/filter validation, HMAC signature generation, signature verification, and changed/missing-event detection.

- [x] **Step 2: Run the domain tests and confirm RED**

  Run: `PYTHONPATH=. .venv/bin/python -m pytest -q tests/system/test_governance_audit.py`

  Expected: collection fails because `app.core.system.governance_audit` does not exist.

- [x] **Step 3: Implement the minimum domain service**

  Use canonical UTF-8 JSON with sorted keys and compact separators. Hash each normalized event with SHA-256, hash the ordered event digests into a root, and sign the seal payload with HMAC-SHA256. Reject secrets shorter than 32 bytes, unsupported categories, invalid ISO timestamps, `page_size > 100`, seal windows with `end <= start`, and source result sets larger than 50,000 events.

- [x] **Step 4: Run the domain tests and confirm GREEN**

  Run: `PYTHONPATH=. .venv/bin/python -m pytest -q tests/system/test_governance_audit.py`

---

### Task 2: PostgreSQL Repository and Evidence-Seal Persistence

**Files:**
- Create: `migrations/versions/20260730_360_governance_audit_seals.py`
- Create: `app/core/system/governance_audit_repository.py`
- Test: `tests/system/test_governance_audit_repository.py`
- Test: `tests/integration/test_governance_audit_postgres.py`
- Modify: `tests/test_database_migrations.py`

**Interfaces:**
- Consumes: the six fixed category names and an inclusive UTC time window.
- Produces: `SqlAlchemyGovernanceAuditRepository.fetch_events(...)`, `category_counts(...)`, `save_seal(...)`, `get_seal(...)`, and `list_seals(...)`.

- [x] **Step 1: Write failing repository and migration tests**

  Assert safe, parameterized queries for:

  - `auth_audit_events` → `authentication`;
  - `ingestion_jobs` → `ingestion`;
  - entity reviews and rollbacks → `entity_resolution`;
  - ontology publish runs, semantic-code approvals, and quality-profile publications → `publication`;
  - quality-issue timeline → `remediation`;
  - `knowledge_query_audits` → `knowledge_query`.

  Assert that returned detail is an explicit allowlist and SQL never selects raw question text, IP address, user agent, source config, credentials, remediation notes, evidence, or snapshots.

- [x] **Step 2: Run the repository tests and confirm RED**

  Run: `PYTHONPATH=. .venv/bin/python -m pytest -q tests/system/test_governance_audit_repository.py tests/test_database_migrations.py`

- [x] **Step 3: Add the append-only seal table and repository**

  Persist `uid`, `period_start`, `period_end`, `categories`, `event_count`, `root_hash`, `signature`, `key_version`, `sealed_by`, and `created_at`. The application exposes no update/delete path; downgrade retains evidence. Query each source independently with safe projections, normalize timestamps to UTC ISO-8601, merge deterministically, and apply filters/pagination after normalization.

- [x] **Step 4: Run repository tests and a real PostgreSQL integration**

  Run the repository tests, then run the integration test against the local migrated PostgreSQL database. The integration test must insert one safe fixture for every category, create a seal, verify it, mutate one fixture inside a transaction, observe verification failure, roll back, and verify again.

---

### Task 3: Admin-Only API, Safe Error Boundary, and Security Checks

**Files:**
- Create: `app/api/system/governance_audit.py`
- Modify: `app/api/system/__init__.py`
- Modify: `app/core/system/permissions.py`
- Modify: `app/config/config.py`
- Modify: `app/__init__.py`
- Test: `tests/system/test_governance_audit_api.py`
- Test: `tests/test_permission_matrix.py`
- Test: `tests/system/test_safe_error_boundary.py`

**Interfaces:**
- Produces:
  - `GET /api/system/governance-audit/security-checks`
  - `GET /api/system/governance-audit/coverage`
  - `GET /api/system/governance-audit/events`
  - `GET /api/system/governance-audit/seals`
  - `POST /api/system/governance-audit/seals`
  - `POST /api/system/governance-audit/seals/{seal_uid}/verify`

- [x] **Step 1: Write failing API, permission, and error-boundary tests**

  Assert admin access, viewer/editor denial, server-derived actor identity, safe 400/503 responses, fixed categories, bounded query parameters, explicit “tamper-evident” wording, and that exceptions containing passwords, bearer tokens, database URLs, or API keys never appear in the JSON response or log message.

- [x] **Step 2: Run tests and confirm RED**

  Run: `PYTHONPATH=. .venv/bin/python -m pytest -q tests/system/test_governance_audit_api.py tests/system/test_safe_error_boundary.py tests/test_permission_matrix.py`

- [x] **Step 3: Implement API and configuration**

  Add `AUDIT_READ` and `AUDIT_SEAL` permissions to admin only. Load `AUDIT_EVIDENCE_SECRET` and `AUDIT_EVIDENCE_KEY_VERSION` without logging values; if the dedicated secret is absent, use `SECRET_KEY` only for local engineering and report a warning check. Security checks report booleans and reason codes for encrypted datasource storage, legacy plaintext cleanup, secret strength, secure error boundary, safe response headers, six-category audit availability, and seal-key readiness; they never expose configuration values.

- [x] **Step 4: Harden the global error and response boundary**

  Log a redacted bounded classification plus correlation ID, return only “服务器内部错误” and the correlation ID, and add `Referrer-Policy`, `Permissions-Policy`, and `Cache-Control: no-store` for authenticated/system audit responses. Preserve existing file-download behavior.

- [x] **Step 5: Run API tests and confirm GREEN**

  Re-run the tests from Step 2.

---

### Task 4: Audit and Runtime Evidence Workbench

**Files:**
- Create: `frontend/src/api/governanceAudit.js`
- Create: `frontend/src/views/systemManage/governanceAudit/governanceAuditModel.js`
- Create: `frontend/src/views/systemManage/governanceAudit/index.vue`
- Create: `frontend/tests/governance-audit-model.test.mjs`
- Modify: `frontend/src/router/routes.js`
- Test: `tests/system/test_governance_audit_frontend_contract.py`

**Interfaces:**
- Consumes: the six system API endpoints from Task 3.
- Produces: an admin-only “审计与运行证据” page with security check cards, six-category coverage, event filters/table, seal history, create-seal dialog, and verify action.

- [x] **Step 1: Write failing frontend model and contract tests**

  Assert fixed Chinese category labels, safe status labels, UTC/local time formatting, “签名封存用于检测篡改，不等于阻止数据库管理员修改” disclosure, no raw evidence/question/detail columns, admin-only route permission, and all six APIs.

- [x] **Step 2: Run tests and confirm RED**

  Run:

  `node --test frontend/tests/governance-audit-model.test.mjs`

  `PYTHONPATH=. .venv/bin/python -m pytest -q tests/system/test_governance_audit_frontend_contract.py`

- [x] **Step 3: Implement the workbench**

  Default to the last 30 days, page at 20 rows, display coverage as “有记录/暂无记录” rather than a false percentage, mark failed security checks clearly, disable sealing when the seal key is not ready, and show the stored root hash/signature only as shortened copyable identifiers.

- [x] **Step 4: Run frontend tests, changed-file lint, and production build**

  Run the Node test, Python contract test, ESLint only on changed frontend files, and the frontend production build.

---

### Task 5: Architecture, Ledger, Release-Copy Parity, and Targeted Acceptance

**Files:**
- Modify: `docs/DATAOPS_PHASE1_3_MONTH_WORK_PLAN_20260729.md`
- Modify: `docs/FUNCTION_MODULE_CENSUS_20260726.md`
- Modify: `docs/architecture/DATA_MODEL.md`
- Modify: `docs/architecture/OPENAPI.yaml`
- Create: `docs/validation/WP12_SECURITY_AUDIT_EVIDENCE.md`
- Modify: `tests/test_architecture_artifacts.py`
- Mirror changed backend files under: `deployment/app/`

**Interfaces:**
- Produces: a reviewable WP12 engineering receipt that distinguishes local engineering completion from enterprise security review and retention/real-data acceptance.

- [x] **Step 1: Write failing architecture and parity assertions**

  Assert all six endpoints, the seal table, SEC-08/SEC-10/SEC-11/SEC-12 status wording, the WP12 implementation-status row, and byte-for-byte parity for changed backend files.

- [x] **Step 2: Run architecture tests and confirm RED**

  Run: `PYTHONPATH=. .venv/bin/python -m pytest -q tests/test_architecture_artifacts.py tests/system/test_governance_audit_frontend_contract.py`

- [x] **Step 3: Update documents and release copy**

  Record the exact safe-field policy, six-category coverage, HMAC/key-rotation boundary, 50,000-event seal limit, five-year retention design target, current local test evidence, and the enterprise gates: security-owner approval, production secret/key custody, retention/archival job, real event sampling, and external backup/restore under WP13.

- [x] **Step 4: Run targeted WP12 verification**

  Run all new WP12 Python tests plus directly affected permission, migration, architecture, redaction, datasource-credential, and auth tests; run Ruff on changed Python files, the frontend Node test, changed-file ESLint, and frontend production build. Do not run the full suite.

- [x] **Step 5: Run local browser acceptance**

  Rebuild only local backend/frontend if necessary. As admin, verify the page loads, six categories are visible, no unsafe columns exist, creating a bounded seal succeeds, verifying it returns intact, requests return 200, and the browser console has no errors. Record actual evidence; do not substitute automated tests for browser checks.

- [x] **Step 6: Re-read this plan and commit**

  Confirm each requirement and boundary, ensure `git status` contains only WP12 changes, then commit locally with `feat: add tamper-evident governance audit`.