# 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)