ADR 0007: Audit Storage and Retention Policy

Status

Accepted

Context

We need a safe, auditable record of executed queries to support evidence-first reviews, incident analysis, and compliance requests. Today, most traces live in logs or transient artifacts, which is fragile and hard to govern.

Decision

Introduce an operational audit store (Postgres recommended; local dev/CI uses Postgres, production uses the same store) that captures sanitized query metadata and redacted SQL/results with an explicit retention policy.

Retention

• Default retention: 30 days.
• Automatic purge job (scheduled task) required.

Data Stored (Sanitized)

• Request identifiers, timestamps, brand/profile, model/provider.
• Redacted SQL (no raw literals or PII).
• Result summaries only (row count, column list, optional top-N preview if redaction is guaranteed).
• Error category and validation outcome.

Relationship to TraceService

• TraceService remains the execution trace system.
• Audit storage persists redacted SQL/result metadata for retention and review.

Constraints and Safety

• Never store raw SQL or raw results that could contain PII.
• Public logs must not contain stored SQL/results; only summary counts.
• Storage is for audit/troubleshooting, not analytics reuse.

Feature Flag

• JORVIS_AUDIT_STORAGE_ENABLED=false (default)

Alternatives

• Keep logs-only (rejected: insufficient auditability).
• Store full results (rejected: PII/compliance risk).

DoD / Evidence

• Redaction tests for SQL and result previews.
• Retention purge verified locally.
• Sanitized evidence in PR output.