Activity Log
Data Entity
Description
Immutable audit trail entity recording all state changes and field modifications to an activity record over its lifecycle. Used for coordinator correction audits, Bufdir compliance verification, and duplicate detection context queries.
Data Structure
| Name | Type | Description | Constraints |
|---|---|---|---|
id |
uuid |
Surrogate primary key, generated server-side on insert. Never reused or reassigned. | PKrequiredunique |
activity_id |
uuid |
Foreign key referencing the activities table. Every log entry is bound to exactly one activity record. Indexed for fast per-activity audit retrieval. | required |
action |
enum |
Categorises the type of mutation that triggered this log entry. Drives UI rendering in audit views and filtering logic in Bufdir report generation. | required |
changed_by |
uuid |
User ID of the actor who triggered the state change. Can be a peer mentor (own activity), coordinator (correction/approval), or a system service account (auto-approval, cron job). Foreign key to users table. | required |
actor_role |
enum |
Role of the actor at the time of the change. Stored denormalised to preserve the historical role context even if the user's role changes later. Critical for Bufdir compliance verification. | required |
organization_id |
uuid |
Organization scope of the actor at the time of the change. Denormalised for RLS-enforced coordinator audit queries and cross-org duplicate detection scoping. | required |
old_values |
json |
JSONB snapshot of the activity record's changed fields before this mutation. NULL on 'created' and 'draft_saved' actions. Stores only the fields that changed, not the full record, to reduce storage. Format: {field_name: previous_value}. | - |
new_values |
json |
JSONB snapshot of the activity record's changed fields after this mutation. NULL on 'deleted' action. Stores only the fields that changed. Format: {field_name: new_value}. | - |
change_reason |
text |
Mandatory free-text justification for 'corrected' and 'rejected' actions. Optional context note for 'approved' actions. NULL for system-initiated actions (auto-approval, draft_saved). Stored for coordinator accountability and Bufdir audit trail. | - |
changed_at |
datetime |
UTC timestamp of when this log entry was created. Set server-side via default now() to prevent client clock manipulation. Primary ordering column for audit timeline display. | required |
client_metadata |
json |
Optional JSONB bag for contextual metadata captured at write time: app version, platform (iOS/Android), and feature context (e.g., 'bulk_registration', 'proxy_registration'). Used for debugging and analytics, not for compliance decisions. | - |
is_system_generated |
boolean |
True when the log entry was created by a system process (auto-approval edge function, cron job, batch import) rather than a human actor. Allows audit views to filter out system noise and focus on human decisions. | required |
Database Indexes
idx_activity_logs_activity_id
Columns: activity_id
idx_activity_logs_activity_id_changed_at
Columns: activity_id, changed_at
idx_activity_logs_changed_by
Columns: changed_by
idx_activity_logs_action
Columns: action
idx_activity_logs_organization_id_changed_at
Columns: organization_id, changed_at
idx_activity_logs_changed_at
Columns: changed_at
Validation Rules
activity_id_references_existing_activity
error
Validation failed
changed_by_references_existing_user
error
Validation failed
action_enum_value_enforced
error
Validation failed
changed_at_not_in_future
error
Validation failed
old_new_values_valid_json
error
Validation failed
change_reason_max_length
warning
Validation failed
no_duplicate_log_entries_for_same_action_at_same_instant
warning
Validation failed
rls_policy_enforced_read
error
Validation failed
Business Rules
immutable_after_insert
Activity log entries are strictly immutable once written. No UPDATE or DELETE operations are permitted from application code. Deletion is only possible via cascade when the parent activity is hard-deleted by a database migration or administrator-level cleanup. This guarantees an unbroken, tamper-evident audit chain required for Bufdir compliance.
log_entry_required_on_every_state_change
Every transition of an activity record's status or field values MUST produce a corresponding activity_logs entry within the same database transaction. If the log insert fails, the parent activity mutation must also be rolled back. This prevents gaps in the audit trail.
old_values_null_on_create
When action = 'created', the old_values field MUST be NULL because no prior state exists. Storing a non-null value for old_values on a creation event is a data integrity violation.
new_values_null_on_delete
When action = 'deleted', the new_values field MUST be NULL because no subsequent state exists. The old_values field must contain the final state of all non-null fields before deletion for recovery purposes.
change_reason_required_for_rejection_and_correction
When action = 'rejected' or 'corrected', the change_reason field must be non-null and non-empty (minimum 10 characters). Coordinators must provide a substantive justification for data quality accountability and Bufdir audit requirements.
actor_role_matches_action_scope
Only actors with coordinator or admin roles may create log entries with action = 'approved', 'rejected', or 'corrected'. A peer_mentor actor may only create 'created', 'updated', 'draft_saved', and 'submitted' entries. System actors may create 'approved' entries (auto-approval). Violations indicate authorization bypass.
organization_scope_consistency
The organization_id on the log entry must match the organization_id on the parent activities record. Mismatches indicate a cross-organization data leak and must be rejected.
proxy_registration_attributed_to_coordinator
When a coordinator submits an activity on behalf of a peer mentor (proxy/bulk registration), the changed_by field must reference the coordinator's user ID, not the peer mentor's ID. The activity record itself carries the peer mentor's user_id as the subject. This preserves delegation accountability.
bufdir_report_inclusion
Bufdir report generation queries activity_logs to determine which activities were active (not deleted) within a reporting period. Log entries with action = 'deleted' within the period cause the parent activity to be excluded from grant calculations. This rule is enforced at query time, not at write time.
CRUD Operations
Storage Configuration
Entity Relationships
Every modification to an activity creates an immutable audit log entry