pg_cron.
Database Schema
New Tables
| Table | Purpose |
|---|---|
ai_generation_queue | Job queue — rows represent pending, running, completed, or failed generation requests |
ai_provider_health | Circuit breaker state per provider — tracks consecutive failures, circuit status, and last probe time |
ai_provider_costs | Per-provider daily call counts, token usage, and cost tracking |
ai_content_budgets | Per-tenant monthly budget limits and current spend |
ai_alert_log | Record of Slack alerts sent (circuit open, budget warnings) to prevent duplicate notifications |
Extended Tables
These existing tables received new columns (provider, content_hash, fallback_used, cost_usd):
| Table | Content Type | Scope |
|---|---|---|
ai_gap_narratives | gap_narrative | Per tenant |
ai_remediation_playbooks | remediation_playbook | Per control + cloud provider (shared across tenants) |
ai_evidence_suggestions | evidence_suggestion | Per evidence item |
ai_policy_suggestions | policy_suggestion | Per policy version |
Content Types
gap_narrative
One per tenant. Explains the tenant’s overall risk posture, key gaps, and recommended next steps. Hash inputs: latest risk snapshot (overall_risk, completeness_pct, answered_count, confidence) + all control IDs/statuses + max evidence_link timestamp.
remediation_playbook
One per control + cloud provider combination. Shared across tenants — if two tenants use the same control on AWS, they share the playbook. Hash inputs: control’scontrol_key + title + description + implementation_notes + cloud_provider.
evidence_suggestion
One per evidence item. Suggests what evidence to upload and how to collect it. Hash inputs: evidence item’stitle + description + evidence_type + updated_at.
policy_suggestion
One per policy version. Suggests improvements to policy language and coverage. Hash inputs:policy_id + version_id.
Edge Function Worker
The worker is a Supabase Edge Function named
ai_content_worker, running in Deno runtime.| Setting | Value |
|---|---|
| Name | ai_content_worker |
| Trigger | pg_cron every 60 seconds |
| Batch size | 3 jobs per cycle (configurable via BATCH_SIZE) |
| Max execution | 25 seconds (configurable via MAX_DURATION_MS) |
| Idle cost | ~200ms execution when queue is empty |
Worker Flow
Claim jobs
Calls
rpc_claim_ai_jobs to atomically claim up to BATCH_SIZE queued jobs (sets status to running, assigns claimed_at).Get available providers
Calls
rpc_get_available_providers to get providers ordered by priority, excluding those with open circuits.Generate content
For each job, tries providers in order. On success, calls
rpc_complete_ai_job. On failure, tries the next provider. If all fail, calls rpc_fail_ai_job.Cron Jobs
All times are UTC. These jobs are registered viapg_cron in Supabase.
| Name | Schedule | Purpose |
|---|---|---|
ai-content-worker | * * * * * | Drain queue every 60s via Edge Function invocation |
ai-reclaim-stuck-jobs | */5 * * * * | Reset jobs stuck in running for >10 minutes back to queued |
ai-alert-circuit-open | */5 * * * * | Send Slack alert when any provider circuit is open |
ai-alert-budget-warnings | 0 * * * * | Send Slack alert when tenant hits 80% of monthly budget |
ai-budget-daily-reset | 1 0 * * * | Reset daily user-regen counter |
ai-budget-monthly-reset | 5 0 1 * * | Reset monthly cost counter |
ai-provider-daily-reset | 2 0 * * * | Reset daily provider call counters |
ai-nightly-backfill | 30 3 * * * | Enqueue regeneration jobs for all tenants (runs after risk recompute) |
Event Triggers
Jobs are enqueued automatically when:- Risk scores change — the nightly risk recompute enqueues
gap_narrativejobs for all tenants. - User requests regeneration —
rpc_get_or_request_narrativewithforce_refresh: trueenqueues a new job. - Nightly backfill — the
ai-nightly-backfillcron enqueues jobs for any tenant whose content hash has changed. - Direct enqueue —
rpc_enqueue_ai_jobcan be called for any content type.
Content-Hash Caching
Before generating content, the system computes a hash of the input data. If the hash matches the existing content’scontent_hash, the job is skipped — no provider call is made, no cost is incurred.
The hash function is compute_gap_narrative_hash(p_tenant_id) for gap narratives. Each content type has its own hash computation based on the inputs listed above.