Implement Audit Trail for SEMOSS and Monolith

[ibrahimmahaba]

Implement OWASP/NIST-Aligned Audit Trail for SEMOSS

Summary

Implement a centralized audit trail for SEMOSS and Monolith that records security-sensitive and administrative actions in UserTrackingDatabase, following OWASP Logging Cheat Sheet and NIST SP 800-92 guidance.

References:

• OWASP Logging Cheat Sheet: https://cheatsheetseries.owasp.org/cheatsheets/Logging_Cheat_Sheet.html#design-implementation-and-testing
• NIST SP 800-92: https://nvlpubs.nist.gov/nistpubs/Legacy/SP/nistspecialpublication800-92.pdf

Goals

• Create a centralized audit event table for user, permission, project, workspace, and engine lifecycle activity.
• Ensure audit logging is best-effort and does not break the main user flow.
• Allow admins to query audit events.
• Capture enough context to answer:
  • Who performed the action?
  • What action was performed?
  • What resource was affected?
  • When did it happen?
  • Was it successful or failed?
  • What changed?
  • Which user or group was affected, when applicable?
  • Which request/session caused it, when available?
• Align the audit trail design with OWASP and NIST logging guidance.

Scope

Implement the audit trail as one complete backend feature across:

• Semoss
• Monolith

Frontend audit viewing can be handled separately unless required for admin validation.

Proposed Audit Table

Create/extend USER_AUDIT_EVENTS in UserTrackingDatabase.

Required columns:

EVENT_ID
EVENT_TIME
EVENT_OCCURRED_TIME
EVENT_TYPE
ACTION
STATUS
SEVERITY
CATEGORY

ACTOR_USER_ID
ACTOR_USER_TYPE
ACTOR_USER_NAME
ACTOR_IS_ADMIN

SUBJECT_USER_ID
SUBJECT_USER_TYPE
SUBJECT_USER_NAME

SESSION_ID_HASH
REQUEST_ID
IP_ADDR
USER_AGENT
HTTP_METHOD
REQUEST_PATH
HTTP_STATUS

TARGET_TYPE
TARGET_ID
TARGET_NAME

PROJECT_ID
ENGINE_ID
INSIGHT_ID
ROOM_ID

OLD_VALUE
NEW_VALUE
DETAILS

ERROR_CODE
ERROR_MESSAGE

SOURCE_APP
SOURCE_MODULE
SOURCE_CLASS

HASH_PREVIOUS
HASH_CURRENT

Column Definitions

Column	Purpose
EVENT_ID	Unique audit event id
EVENT_TIME	Time audit row was written
EVENT_OCCURRED_TIME	Time the business event occurred, if different
EVENT_TYPE	High-level event name, e.g. LOGIN, PERMISSION_UPDATE
ACTION	Specific action performed
STATUS	SUCCESS, FAILURE, DENIED, ERROR
SEVERITY	LOW, MEDIUM, HIGH, CRITICAL
CATEGORY	AUTH, AUTHZ, USER_ADMIN, PROJECT, WORKSPACE, ENGINE, AUDIT, etc.
ACTOR_*	User who performed the action
SUBJECT_USER_*	User affected by the action, e.g. permission grantee or created user
SESSION_ID_HASH	Hashed session id, not raw session id
REQUEST_ID	Request correlation id
IP_ADDR	Client IP address
USER_AGENT	Client user agent
HTTP_METHOD	Request method
REQUEST_PATH	Request path
HTTP_STATUS	Response status when available
TARGET_*	Resource affected by the event
PROJECT_ID	Project context
ENGINE_ID	Engine context
INSIGHT_ID	Insight context
ROOM_ID	Workspace/room context
OLD_VALUE	Sanitized JSON before-value
NEW_VALUE	Sanitized JSON after-value
DETAILS	Sanitized event-specific JSON
ERROR_CODE	Stable error code, if failed
ERROR_MESSAGE	Sanitized error summary
SOURCE_*	Application/module/class that emitted the audit event
HASH_PREVIOUS / HASH_CURRENT	Optional tamper-evidence chain

Event Types To Implement

Authentication

• LOGIN
• LOGOUT
• LOGIN_FAILED

Authorization / Permissions

• AUTHORIZATION_DENIED
• PERMISSION_ADD
• PERMISSION_UPDATE
• PERMISSION_DELETE
• ACCESS_REQUEST_APPROVE
• ACCESS_REQUEST_REJECT

Covered resources:

• Project permissions
• Engine permissions
• Insight permissions
• Group project permissions
• Group engine permissions
• Group insight permissions

User Administration

• USER_CREATE
• USER_UPDATE
• USER_DELETE
• USER_ACTIVATE
• USER_DEACTIVATE
• USER_ROLE_UPDATE
• USER_PASSWORD_RESET

Must include actions from the admin member dashboard.

Group Administration

• GROUP_CREATE
• GROUP_UPDATE
• GROUP_DELETE
• GROUP_MEMBER_ADD
• GROUP_MEMBER_REMOVE

Project Lifecycle

• PROJECT_CREATE
• PROJECT_UPLOAD
• PROJECT_UPDATE
• PROJECT_DELETE

Workspace Lifecycle

• WORKSPACE_CREATE
• WORKSPACE_UPDATE
• WORKSPACE_DELETE

Engine Lifecycle

• ENGINE_CREATE
• ENGINE_UPDATE
• ENGINE_DELETE
• MODEL_CREATE
• VECTOR_CREATE
• STORAGE_CREATE
• FUNCTION_CREATE
• GUARDRAIL_CREATE

Metadata / Visibility Updates

• Engine display name update
• Engine canonical name update
• Engine global flag update
• Engine discoverable flag update
• Engine visibility update
• Engine metadata update
• Database metadata update

These can be recorded as:

EVENT_TYPE = ENGINE_UPDATE
CATEGORY = ENGINE
DETAILS.field = displayName | name | global | discoverable | visibility | metadata

Audit Access

• AUDIT_QUERY
• AUDIT_EXPORT

Implementation Requirements

Backend Table Creation

• Add USER_AUDIT_EVENTS to UserTrackingDatabase.
• Ensure schema creation works when USER_TRACKING_ENABLED=true.
• Ensure system does not crash when USER_TRACKING_ENABLED=false.

Audit Utility

Create or update a central audit utility that supports:

• Success audit events
• Failure audit events
• Actor extraction
• Subject user extraction
• Target resource context
• Request context
• Old/new value JSON
• Details JSON
• Error code/message
• Sanitization/redaction
• Session id hashing
• Best-effort insert behavior

Audit insert failures must be logged to application logs but must not break the primary business action.

Request Context

Add request context collection where available:

• request id
• client IP
• user agent
• HTTP method
• request path
• HTTP status

For non-request background cleanup events, missing request fields are acceptable.

Sanitization / Redaction

Audit logging must not store:

• passwords
• access tokens
• refresh tokens
• API keys
• secrets
• full connection strings
• raw private keys
• sensitive model prompts/responses
• sensitive SQL result data
• sensitive file contents

Sensitive keys in JSON should be redacted before insert.

Example:

{
  "apiKey": "[REDACTED]",
  "password": "[REDACTED]"
}

Permission Event Semantics

For permission changes:

• TARGET_* must represent the resource being permissioned.
• SUBJECT_USER_* must represent the affected user/grantee when applicable.
• Group permission changes should include group id/type in DETAILS or equivalent subject fields if group subject columns are added.
• OLD_VALUE should contain previous permission where available.
• NEW_VALUE should contain new permission where available.
• Updates must produce one PERMISSION_UPDATE row only.
• Internal implementation cleanup must not produce false PERMISSION_DELETE rows.

Project / Engine Event Semantics

For lifecycle events:

• TARGET_ID must be the stable project/engine id.
• TARGET_NAME should be populated when available.
• PROJECT_ID or ENGINE_ID should be populated for joins.
• DETAILS should include useful non-sensitive metadata such as type, global flag, mode, resource counts, or metadata keys.

Failure Logging

Log failure events for sensitive flows, including:

• failed login
• authorization denied
• invalid permission changes
• failed create/upload/delete
• metadata validation failure
• access request failure

Failure events should include:

STATUS = FAILURE | DENIED | ERROR
ERROR_CODE
ERROR_MESSAGE

ERROR_MESSAGE must be sanitized and should not expose secrets or internal stack traces.

Admin Query Reactor

Implement an admin-only reactor to query audit events.

Requirements:

• Only admins can query audit events.
• If user tracking is disabled, return a clear error.
• If user tracking DB is not loaded, return a clear error.
• Support filtering by:
  • event type
  • category
  • status
  • actor user id
  • subject user id
  • target type
  • target id
  • project id
  • engine id
  • insight id
  • date range
• Support sorting by event time.
• Support limit/page size.

Operational Requirements

Based on OWASP and NIST guidance:

• Define audit retention configuration.
• Define who can view audit logs.
• Define whether audit export is allowed and track it as AUDIT_EXPORT.
• Ensure audit logs are protected from unauthorized modification.
• Consider append-only behavior or tamper-evidence using HASH_PREVIOUS and HASH_CURRENT.
• Add monitoring/alert rules for high-risk events:
  • repeated failed login
  • repeated authorization denied
  • admin user created
  • user role changed
  • engine/project deleted
  • audit export
  • audit query spike

Acceptance Criteria

Schema

• USER_AUDIT_EVENTS exists in UserTrackingDatabase.
• Table includes all required V2 audit columns.
• Schema creation works on clean setup.
• Existing deployments can be migrated without data loss.

Config Behavior

• When USER_TRACKING_ENABLED=false, audit writes are no-op and business flows do not crash.
• Admin audit query returns a clear error when user tracking is disabled.
• Admin audit query returns a clear error when user tracking DB is not loaded.

Authentication

• Successful login creates LOGIN event.
• Logout creates LOGOUT event.
• Explicit logout includes IP_ADDR.
• Failed login creates LOGIN_FAILED event with sanitized error.

Permissions

• Project user permission add/update/delete creates correct audit rows.
• Engine user permission add/update/delete creates correct audit rows.
• Insight user permission add/update/delete creates correct audit rows.
• Group project permission add/update/delete creates correct audit rows.
• Group engine permission add/update/delete creates correct audit rows.
• Group insight permission add/update/delete creates correct audit rows.
• Access request approval creates ACCESS_REQUEST_APPROVE.
• Access request rejection creates ACCESS_REQUEST_REJECT.
• Approval that grants permission also records the permission grant.
• Permission update creates only one semantic update event, with no false delete row.
• SUBJECT_USER_ID is populated for user/grantee permission flows.

User / Group Admin

• Adding user from admin member dashboard creates USER_CREATE.
• Updating user from admin member dashboard creates USER_UPDATE.
• Deleting/deactivating user creates USER_DELETE or USER_DEACTIVATE.
• User role/admin status change creates USER_ROLE_UPDATE.
• Admin password reset creates USER_PASSWORD_RESET.
• Group create/update/delete events are recorded.
• Group member add/remove events are recorded.

Project / Workspace

• Project create creates PROJECT_CREATE.
• Project upload creates PROJECT_UPLOAD.
• Project app replace/update creates PROJECT_UPDATE.
• Project delete creates PROJECT_DELETE.
• Workspace create creates WORKSPACE_CREATE.
• Workspace update creates WORKSPACE_UPDATE.
• Workspace delete creates WORKSPACE_DELETE.

Engine

• Generic engine upload creates ENGINE_CREATE.
• Engine delete creates ENGINE_DELETE.
• Database delete creates ENGINE_DELETE with database target type/context.
• Model create creates MODEL_CREATE.
• Vector database create creates VECTOR_CREATE.
• Storage engine create creates STORAGE_CREATE.
• Function engine create creates FUNCTION_CREATE.
• Guardrail engine create creates GUARDRAIL_CREATE.
• Engine metadata/display/global/discoverable/visibility updates create ENGINE_UPDATE.

Failure Events

• Authorization failures create AUTHORIZATION_DENIED.
• Validation failures create appropriate failed event with sanitized error.
• Create/upload/delete failures create failed audit events where applicable.
• Audit logging failure does not break the business operation.

Sanitization

• Passwords are never logged.
• Tokens are never logged.
• API keys are redacted.
• Secrets/connection strings are redacted.
• Raw model prompts/responses are not logged by default.
• Raw SQL result data is not logged.
• Error messages are sanitized and do not include stack traces.

Admin Query

• Non-admin users cannot query audit events.
• Admin users can query audit events.
• Admin users can filter by event type, actor, subject, target, resource id, and date range.
• Querying audit events can itself be logged as AUDIT_QUERY.
• Exporting audit events can be logged as AUDIT_EXPORT.

Tests

• Unit tests for audit utility no-op when tracking disabled.
• Unit tests for audit utility failure isolation.
• Unit tests for sanitizer/redaction.
• Integration test for login/logout audit.
• Integration test for permission add/update/delete.
• Integration test for admin member dashboard user create.
• Integration test for project lifecycle events.
• Integration test for engine lifecycle events.
• Integration test for admin-only audit query.

Notes

This task should be implemented as a complete audit-trail feature, using OWASP and NIST guidance as references. Existing SEMOSS query/model/guardrail tracking mechanisms are outside the scope unless they are explicitly converted into sanitized audit summary events later.