姚章浩
25 lines
1.3 KiB
SQL
25 lines
1.3 KiB
SQL
-- Durable FAQ export state for uncovered support cases.
|
|
ALTER TABLE support_cases
|
|
ADD COLUMN IF NOT EXISTS faq_export_status TEXT NOT NULL DEFAULT 'not_applicable'
|
|
CHECK (faq_export_status IN ('not_applicable', 'pending', 'exporting', 'exported', 'export_failed')),
|
|
ADD COLUMN IF NOT EXISTS faq_export_attempts INTEGER NOT NULL DEFAULT 0
|
|
CHECK (faq_export_attempts >= 0),
|
|
ADD COLUMN IF NOT EXISTS faq_last_attempt_at TIMESTAMPTZ,
|
|
ADD COLUMN IF NOT EXISTS faq_next_attempt_at TIMESTAMPTZ,
|
|
ADD COLUMN IF NOT EXISTS faq_exported_at TIMESTAMPTZ,
|
|
ADD COLUMN IF NOT EXISTS faq_failure_code TEXT NOT NULL DEFAULT '',
|
|
ADD COLUMN IF NOT EXISTS faq_record_id TEXT NOT NULL DEFAULT '';
|
|
|
|
-- Existing cases are intentionally not backfilled. Only cases created after
|
|
-- this migration enter the FAQ workflow automatically.
|
|
ALTER TABLE support_cases
|
|
ALTER COLUMN faq_export_status SET DEFAULT 'pending';
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_support_cases_faq_export_claim
|
|
ON support_cases (faq_export_status, faq_next_attempt_at, created_at, id)
|
|
WHERE faq_export_status IN ('pending', 'export_failed');
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_support_cases_faq_export_stale
|
|
ON support_cases (faq_export_status, faq_last_attempt_at)
|
|
WHERE faq_export_status = 'exporting';
|