DataOps-platform/docs/superpowers/plans/2026-07-30-wp12-security-audit-evidence.md

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.

• 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.

• 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.

• 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.

• 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

• 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.

• 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.

• 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

• 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.

• 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.

• 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.

• 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

• 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.

• 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.

• 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

• 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.

• 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.

• 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.

• 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.