64 lines
1.9 KiB
Markdown
64 lines
1.9 KiB
Markdown
# ADR-0020: Audit Logging Storage
|
|
|
|
## Status
|
|
Accepted
|
|
|
|
## Context
|
|
The platform needs to audit all security-relevant actions:
|
|
- User logins and authentication attempts
|
|
- Permission changes
|
|
- Data modifications
|
|
- Administrative actions
|
|
|
|
Audit logs must be:
|
|
- Immutable (append-only)
|
|
- Queryable
|
|
- Performant (don't slow down operations)
|
|
- Compliant with audit requirements
|
|
|
|
Storage options considered:
|
|
1. **PostgreSQL table** - Simple, queryable, transactional
|
|
2. **Elasticsearch** - Excellent for searching, but additional dependency
|
|
3. **File-based logs** - Simple but hard to query
|
|
4. **External audit service** - Overkill for initial version
|
|
|
|
## Decision
|
|
Store audit logs in **PostgreSQL append-only table** with JSON metadata:
|
|
|
|
1. **Table structure**: `audit_logs` with columns:
|
|
- `id`, `actor_id`, `action`, `target_id`, `metadata` (JSONB), `timestamp`
|
|
2. **Append-only**: No UPDATE or DELETE operations
|
|
3. **JSON metadata**: Flexible storage for additional context
|
|
4. **Indexing**: Index on `actor_id`, `action`, `timestamp` for queries
|
|
|
|
**Rationale:**
|
|
- Simple (no additional infrastructure)
|
|
- Queryable via SQL
|
|
- Transactional (consistent with other data)
|
|
- JSONB provides flexibility for metadata
|
|
- Can migrate to Elasticsearch later if needed
|
|
- Good performance for typical audit volumes
|
|
|
|
## Consequences
|
|
|
|
### Positive
|
|
- Simple implementation
|
|
- Queryable via SQL
|
|
- No additional infrastructure
|
|
- Transactional consistency
|
|
- Can archive old logs if needed
|
|
|
|
### Negative
|
|
- Adds load to primary database
|
|
- May need archiving strategy for large volumes
|
|
- Less powerful search than Elasticsearch
|
|
|
|
### Implementation Notes
|
|
- Create `audit_logs` table via Ent schema
|
|
- Use JSONB for metadata column (PostgreSQL-specific)
|
|
- Add indexes: `(actor_id, timestamp)`, `(action, timestamp)`
|
|
- Implement async logging (optional, via channel) for high throughput
|
|
- Consider partitioning by date for large volumes
|
|
- Add retention policy (e.g., archive after 1 year)
|
|
|