Project C — Data Governance & Security (Masking, RBAC, Audit Logging)

Overview

Add role-based access control, PII column masking, and audit logging to an existing schema — and specifically, instrument the portfolio_admin write-path built earlier this session (2026-08-18) with real audit logging, so the project is partly about auditing this portfolio's own system rather than only a synthetic exercise.

Overlaps with dba-project-ideas.md #5 ("Permissions/security audit across the whole stack") for the RBAC-audit half — that doc's framing (systematically checking SHOW GRANTS across all 7+ existing MySQL users: portfolio_web, portfolio_admin, pipeline_writer, metabase_app, metabase_reader, etc.) is the more specific version of that half of this project. This doc adds the masking and audit-logging halves, which aren't in that doc.

What It Demonstrates

Data privacy/governance is named as a top-tier 2026 hiring driver in this session's research — data masking, auditing, and lifecycle management specifically. This is also the most differentiated project relative to the existing 3 portfolio projects, which lean modeling/pipeline/tuning — none of them touch security/governance.

Prerequisites / What We Need

• A least-privilege MySQL user audit script (connects as root, runs SHOW GRANTS for every user, flags anything broader than expected) — this is dba-project-ideas.md #5's deliverable, reused here as a starting artifact.
• A column-level masking strategy for at least one real PII-adjacent column — project1_jobs doesn't have obvious PII, so this may need either: (a) adding a synthetic PII column to demonstrate the technique cleanly, or (b) using the admin panel's data (e.g. blog author info, if any) as a more realistic candidate. Decide which before starting.
• An audit log table/mechanism for the portfolio_admin write path (INSERT/UPDATE on projects, project_links, blog_posts) — a trigger-based or application-level log of who (well — "the tailnet identity from Tailscale-User-Login") changed what and when.
• A view into the audit log — even a simple read-only page, or just a documented set of queries, doesn't need a full UI.

Plan

1. Run the least-privilege audit across all existing MySQL users; write up findings (this alone is a real, useful deliverable even before the rest of the project).
2. Design and implement column-level masking for the chosen PII-adjacent column (dynamic masking via a view, or static masking for non-production copies — pick one and justify it).
3. Add an audit log (trigger-based AFTER INSERT/AFTER UPDATE triggers writing to an audit_log table is the standard pattern) to the tables portfolio_admin can write to.
4. Wire the Tailscale-User-Login header value through into the audit log so entries are attributable to a real identity, not just "someone."
5. Write a short governance policy doc: who can access what, why, and how it's enforced (tie this back to the actual grants from step 1).

Deliverables

• SHOW GRANTS audit report across all MySQL users, with any findings fixed (not just noted).
• Masking implementation + a before/after query showing masked vs. unmasked views for different roles.
• A working audit log with real entries from actual admin-panel usage.
• A short written governance policy document.