Architect Supabase pg_cron Extension Integration for Automated Deployment Cleanup

[temma02]

Description

Integrate the Supabase pg_cron extension to schedule automated cleanup of stale deployment records, orphaned resources, and expired data directly within the database.

Requirements and Context

• Reliability: Cleanup must run reliably on schedule
• Testing: Test cleanup job correctness
• Documentation: Document the scheduled cleanup jobs

Suggested Execution

Branch: feat/issue-117-supabase-pgcron-cleanup

Implement Changes

• Add pg_cron scheduled jobs via migration for deployment cleanup
• Clean up stale deployment records and orphaned resources
• Ensure cleanup respects the soft-delete retention window
• Log cleanup job execution for observability

Test and Commit

• Test cleanup job correctly removes only eligible records
• Test that recent records are preserved
• Document the scheduled cleanup jobs

Example Commit Message

feat(database): integrate pg_cron for automated deployment cleanup

- Add pg_cron scheduled cleanup jobs via migration
- Clean up stale records and orphaned resources
- Respect soft-delete retention window

Guidelines

• Respect retention windows in cleanup logic
• Log job execution for observability
• Test cleanup eligibility boundaries