[Feature]: Audit Logging #5

New Issue

jramos · 2026-01-28T13:34:21-07:00

jramos commented

2026-01-28 13:34:21 -07:00

Problem:
No visibility into who added, modified, or deleted CVE entries. Can't track accountability or investigate issues.

Describe the solution you'd like

Solution:
Create an audit log table tracking:

User, action (create/update/delete), timestamp
CVE ID, vendor, and what changed
Viewable log page for admins

Describe alternatives you've considered

Alternatives:

Database triggers (less flexible)
External logging service (unnecessary complexity)

Additional context

Context:
Requires User Authentication to be completed first. Assign to v1.1 milestone.

### Is your feature request related to a problem? **Problem:** No visibility into who added, modified, or deleted CVE entries. Can't track accountability or investigate issues. ### Describe the solution you'd like **Solution:** Create an audit log table tracking: - User, action (create/update/delete), timestamp - CVE ID, vendor, and what changed - Viewable log page for admins ### Describe alternatives you've considered **Alternatives:** - Database triggers (less flexible) - External logging service (unnecessary complexity) ### Additional context **Context:** Requires User Authentication to be completed first. Assign to v1.1 milestone.

jramos added this to the Release v1.1 milestone 2026-01-28 13:34:21 -07:00

jramos added this to the Version 1.1 Release project 2026-01-28 14:00:48 -07:00

jramos moved this to To Do in Version 1.1 Release on 2026-01-28 14:01:04 -07:00

jramos added a new dependency 2026-01-28 15:17:56 -07:00

#4 [Feature]: User Authentication

jramos moved this to In Progress in Version 1.1 Release on 2026-01-29 15:20:23 -07:00

jramos commented

2026-01-30 12:04:58 -07:00

Audit Logging Feature - Automated Test Runner
==============================================
API Base : http://localhost:3001/api
DB Path  : backend/cve_database.db

Preflight checks passed.

── 1. Database & Schema ──
  PASS  1.1 audit_logs table exists with all columns
  PASS  1.2 All four audit indexes exist
  PASS  1.3 Migration script exists (idempotency requires manual run)
  PASS  1.4 Migration script contains backup logic
  PASS  1.5 setup.js references audit_logs table

── Setup: Authenticate test accounts ──
  OK    Admin login successful
  Created test account: _test_editor1 (editor)
  Created test account: _test_viewer1 (viewer)
  OK    Editor login successful
  OK    Viewer login successful

── 2. Authentication Audit Logging ──
  PASS  2.1 Successful login logged with role in details
  PASS  2.2 Failed login (wrong password) logged with reason
  PASS  2.3 Failed login (unknown user) logged with null user_id
  PASS  2.4 Failed login (disabled account) logged with reason
  PASS  2.5 Logout logged
  SKIP  2.6 Login does not block on audit error -- Requires corrupting audit_logs table (see section 12)

── 3. CVE Operation Audit Logging ──
  PASS  3.1 CVE create logged with vendor and severity in details
  PASS  3.2 CVE status update logged with status in details
  PASS  3.3 CVE status update completes without SQL error (vendor bug fixed)
  PASS  3.4 Audit captures acting user (_test_editor1)

── 4. Document Operation Audit Logging ──
  PASS  4.1 Document upload logged
  PASS  4.2 Document delete logged
  PASS  4.3 Upload details contain vendor, type, and filename

── 5. User Management Audit Logging ──
  PASS  5.1 User create logged with username and role
  PASS  5.2 User role update logged
  PASS  5.3 Password change logged with password_changed:true (password not exposed)
  PASS  5.4 Multiple field update logged with all changed fields
  PASS  5.6 User deactivation logged with is_active:false
  PASS  5.5 User delete logged with deleted_username
  PASS  5.7 Self-delete prevented (400) with no audit entry

── 6. API Access Control ──
  PASS  6.1 Admin can query audit logs (200 with logs + pagination)
  PASS  6.2 Editor denied audit logs (403)
  PASS  6.3 Viewer denied audit logs (403)
  PASS  6.4 Unauthenticated denied audit logs (401)
  PASS  6.5 Admin can get actions list (array returned)
  PASS  6.6 Non-admin denied actions list (403)

── 7. API Filtering & Pagination ──
  PASS  7.1 Default pagination (page=1, limit=25, logs<=25)
  PASS  7.2 Custom page size (limit=5)
  PASS  7.3 Page size capped at 100 (got limit=100)
  PASS  7.4 Page 2 returns different entries
  PASS  7.5 Filter by username=admin returns only admin entries
  PASS  7.6 Partial username match (adm matches admin)
  PASS  7.7 Filter by action=login returns only login entries
  PASS  7.8 Filter by entityType=auth returns only auth entries
  PASS  7.9 Date range filter returns entries for today (25 entries)
  PASS  7.10 Combined filters return only matching entries (4)
  PASS  7.11 Empty result set returns logs:[] and total:0
  PASS  7.12 Entries ordered DESC (newest first: id 71 > 67)

── 12. Fire-and-Forget Behavior ──
  SKIP  12.1 Audit failure does not break login -- Requires temporarily corrupting audit_logs table
  SKIP  12.2 Audit failure does not break CVE create -- Requires temporarily corrupting audit_logs table
  SKIP  12.3 Response not delayed by audit -- Requires timing measurement under load

── 13. Data Integrity ──
  PASS  13.1 Audit entries survive user deletion (denormalized username)
  PASS  13.2 All non-null details values are valid JSON
  PASS  13.3 IP addresses captured (48 entries with IP)
  PASS  13.4 All audit entries have non-null created_at
  PASS  13.5 Auth entries have null entity_id

── 14. End-to-End Workflow ──
  FAIL  14.1 Full user lifecycle
        -> Expected: user_create,login,cve_create,user_update,user_delete, Got: user_create,login,user_update,user_delete,
  FAIL  14.2 Expected at least 2 entries for _test_lifecycle, found 1
  PASS  14.3 Security audit trail - 3 failed login attempts recorded

── Cleanup ──
  Removed test CVEs
  Removed test users
  Removed test audit entries

``` Audit Logging Feature - Automated Test Runner ============================================== API Base : http://localhost:3001/api DB Path : backend/cve_database.db Preflight checks passed. ── 1. Database & Schema ── PASS 1.1 audit_logs table exists with all columns PASS 1.2 All four audit indexes exist PASS 1.3 Migration script exists (idempotency requires manual run) PASS 1.4 Migration script contains backup logic PASS 1.5 setup.js references audit_logs table ── Setup: Authenticate test accounts ── OK Admin login successful Created test account: _test_editor1 (editor) Created test account: _test_viewer1 (viewer) OK Editor login successful OK Viewer login successful ── 2. Authentication Audit Logging ── PASS 2.1 Successful login logged with role in details PASS 2.2 Failed login (wrong password) logged with reason PASS 2.3 Failed login (unknown user) logged with null user_id PASS 2.4 Failed login (disabled account) logged with reason PASS 2.5 Logout logged SKIP 2.6 Login does not block on audit error -- Requires corrupting audit_logs table (see section 12) ── 3. CVE Operation Audit Logging ── PASS 3.1 CVE create logged with vendor and severity in details PASS 3.2 CVE status update logged with status in details PASS 3.3 CVE status update completes without SQL error (vendor bug fixed) PASS 3.4 Audit captures acting user (_test_editor1) ── 4. Document Operation Audit Logging ── PASS 4.1 Document upload logged PASS 4.2 Document delete logged PASS 4.3 Upload details contain vendor, type, and filename ── 5. User Management Audit Logging ── PASS 5.1 User create logged with username and role PASS 5.2 User role update logged PASS 5.3 Password change logged with password_changed:true (password not exposed) PASS 5.4 Multiple field update logged with all changed fields PASS 5.6 User deactivation logged with is_active:false PASS 5.5 User delete logged with deleted_username PASS 5.7 Self-delete prevented (400) with no audit entry ── 6. API Access Control ── PASS 6.1 Admin can query audit logs (200 with logs + pagination) PASS 6.2 Editor denied audit logs (403) PASS 6.3 Viewer denied audit logs (403) PASS 6.4 Unauthenticated denied audit logs (401) PASS 6.5 Admin can get actions list (array returned) PASS 6.6 Non-admin denied actions list (403) ── 7. API Filtering & Pagination ── PASS 7.1 Default pagination (page=1, limit=25, logs<=25) PASS 7.2 Custom page size (limit=5) PASS 7.3 Page size capped at 100 (got limit=100) PASS 7.4 Page 2 returns different entries PASS 7.5 Filter by username=admin returns only admin entries PASS 7.6 Partial username match (adm matches admin) PASS 7.7 Filter by action=login returns only login entries PASS 7.8 Filter by entityType=auth returns only auth entries PASS 7.9 Date range filter returns entries for today (25 entries) PASS 7.10 Combined filters return only matching entries (4) PASS 7.11 Empty result set returns logs:[] and total:0 PASS 7.12 Entries ordered DESC (newest first: id 71 > 67) ── 12. Fire-and-Forget Behavior ── SKIP 12.1 Audit failure does not break login -- Requires temporarily corrupting audit_logs table SKIP 12.2 Audit failure does not break CVE create -- Requires temporarily corrupting audit_logs table SKIP 12.3 Response not delayed by audit -- Requires timing measurement under load ── 13. Data Integrity ── PASS 13.1 Audit entries survive user deletion (denormalized username) PASS 13.2 All non-null details values are valid JSON PASS 13.3 IP addresses captured (48 entries with IP) PASS 13.4 All audit entries have non-null created_at PASS 13.5 Auth entries have null entity_id ── 14. End-to-End Workflow ── FAIL 14.1 Full user lifecycle -> Expected: user_create,login,cve_create,user_update,user_delete, Got: user_create,login,user_update,user_delete, FAIL 14.2 Expected at least 2 entries for _test_lifecycle, found 1 PASS 14.3 Security audit trail - 3 failed login attempts recorded ── Cleanup ── Removed test CVEs Removed test users Removed test audit entries ```

jramos closed this issue

2026-01-30 12:32:49 -07:00

jramos moved this to Done in Version 1.1 Release on 2026-02-02 09:51:11 -07:00

Sign in to join this conversation.

1 Participants

Notifications

Due Date

No due date set.

Depends on

#4 [Feature]: User Authentication

jramos/cve-dashboard

Reference: jramos/cve-dashboard#5