Add database schema indexes and data retention strategy

[haasonsaas]

Summary

The schema is missing performance indexes for cleanup queries and has no data retention or partitioning strategy for the audit_events table.

Current state

• db/migrations/0001_init.sql — single migration with all tables
• sessions indexed on (tenant_id, run_id) but not on expires_at
• grants not indexed on expires_at
• approvals not indexed on created_at or expires_at
• artifacts not indexed on expires_at or created_at
• audit_events has no partitioning — will grow unbounded
• No data retention policy for any table

Required work

• Add migration with indexes on expires_at for: sessions, grants, approvals, artifacts (cleanup worker performance)
• Add index on audit_events.created_at for time-range queries
• Add index on grants.session_id for cascade lookups on session revocation
• Implement a data retention strategy:
  • Completed/expired/revoked records older than N days → archive or delete
  • Audit events → partition by month, drop partitions older than retention period
• Switch cleanup worker from offset-based to cursor-based pagination for large tables
• Add LIMIT to ListGrantsBySession (currently unbounded)

Files

• db/migrations/ — new migration file
• internal/store/postgres/repository.go — cursor-based pagination for cleanup queries
• internal/worker/runner.go — use new cursors

Priority

Medium — performance degrades as tables grow.

🤖 Generated with Claude Code

[haasonsaas]

Taking a first production slice of this: add the remaining cleanup-friendly Postgres indexes, add a dedicated grants.session_id index, and bound ListGrantsBySession. The broader retention/partitioning work can follow separately once the immediate query-shape gaps are closed.

[haasonsaas]

Opened the first slice in PR #66: #66

Scope is intentionally narrow:

• add the missing cleanup-path indexes (sessions, grants, approvals, artifacts, audit_events.created_at)
• add a dedicated grants.session_id index
• bound ListGrantsBySession

Validation run locally:

• go test ./internal/store/postgres ./internal/migrate -count=1
• go test ./... -count=1
• golangci-lint run ./...
• git diff --check

The broader retention/partitioning redesign can build on top of this without reopening the basic query-shape gaps.