audit_log
This table stores the tenant-scoped immutable audit trail for end-user and system actions inside DirtView. It records who did what, when they did it, and what resource was affected.
Purpose
The purpose of this table is to preserve a trustworthy historical record of important actions inside a tenant, including workflow changes, uploads, permissions-sensitive actions, and externally visible business events.
What this table does
- Stores one audit event per recorded action
- Captures both machine-readable and human-readable event context
- Tracks affected resources and optional before/after state
- Supports support review, debugging, compliance readiness, and future analytics
- Provides tenant-scoped visibility into system activity
Why this table is defined
DirtView includes sensitive business operations such as RFIs, drawings, employee records, external sharing, and permissions changes. A full audit trail is necessary for accountability, support, and customer trust.
This table is distinct from admin_audit_log, which is reserved
for super-admin platform actions outside tenant scope.
Columns
| Column | Type | Required | Description | Example |
|---|---|---|---|---|
id | uuid | Yes | Primary key for the audit record | audit_001 |
tenant_id | uuid | Yes | Tenant this action belongs to | tenant_001 |
user_id | uuid | No | User who performed the action | user_123 |
user_name | text | Yes | Denormalized display name at write time | James Walker |
action | text | Yes | Machine-readable action code | rfi.status_changed |
description | text | Yes | Human-readable description | James changed RFI-042 from Open to In Review |
resource_type | text | Yes | Resource category affected | rfi |
resource_id | uuid | No | Resource primary key | rfi_001 |
project_id | uuid | No | Optional project context | project_phoenix |
before_state | jsonb | No | Optional pre-change snapshot | {"status":"open"} |
after_state | jsonb | No | Optional post-change snapshot | {"status":"in_review"} |
ip_address | text | No | Origin IP address if available | 203.0.113.25 |
device | text | No | Device or channel | web |
created_at | timestamptz | Yes | When the action occurred | 2026-04-11 14:00:00+00 |
Relationships
- tenant_id → tenants.id
- project_id → projects.id
- resource_id references the resource named by
resource_type
How it is used
- Written by application services after important business actions
- Used for support investigations and tenant activity review
- Can power activity feeds, admin logs, and future AI summarization
- Used to explain how and when records changed over time
Access and security
- This table should be append-only and immutable
- No UPDATE or DELETE path should exist
- Tenant-scoped RLS must apply
- Not every user should see every audit event; exposure can be role-limited
- Account deletion should not remove audit history
Example scenarios
Scenario 1: RFI status change
A PM changes an RFI from Open to In Review, and the status transition is recorded here.
Scenario 2: Drawing upload
A drawing set upload creates an audit entry such as drawing.uploaded.
Scenario 3: User deactivation
A tenant admin deactivates a user, and the event is written with before/after state.
Notes and assumptions
- This is the tenant-scoped counterpart to
admin_audit_log - Business-level logging is usually better handled in application code than only in DB triggers
- Indexing by
(tenant_id, created_at desc)is highly recommended