姚章浩
40 lines
1.8 KiB
SQL
40 lines
1.8 KiB
SQL
-- 000010_question_table_exports: durable outbox for Notable question table async sync
|
|
-- Add trigger_person_snapshot and actor_union_id_snapshot to messages and create question_table_exports table
|
|
|
|
-- 1. Add trigger_person_snapshot and actor_union_id_snapshot columns to messages
|
|
ALTER TABLE messages
|
|
ADD COLUMN IF NOT EXISTS trigger_person_snapshot TEXT NOT NULL DEFAULT '';
|
|
ALTER TABLE messages
|
|
ADD COLUMN IF NOT EXISTS actor_union_id_snapshot TEXT NOT NULL DEFAULT '';
|
|
|
|
-- 2. Create question_table_exports outbox table
|
|
CREATE TABLE IF NOT EXISTS question_table_exports (
|
|
id TEXT PRIMARY KEY,
|
|
tenant_key TEXT NOT NULL,
|
|
channel TEXT NOT NULL,
|
|
turn_id TEXT NOT NULL REFERENCES turns(id),
|
|
status TEXT NOT NULL DEFAULT 'pending'
|
|
CHECK (status IN ('pending', 'exporting', 'exported', 'export_failed')),
|
|
attempts INTEGER NOT NULL DEFAULT 0,
|
|
last_attempt_at TIMESTAMPTZ,
|
|
next_attempt_at TIMESTAMPTZ,
|
|
exported_at TIMESTAMPTZ,
|
|
failure_code TEXT NOT NULL DEFAULT '',
|
|
external_record_id TEXT NOT NULL DEFAULT '',
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
UNIQUE (tenant_key, channel, turn_id)
|
|
);
|
|
|
|
-- 3. Index for claim query: pending/export_failed/exporting ordered by created_at
|
|
-- Covers pending, export_failed (when next_attempt_at is NULL or <= NOW()),
|
|
-- and stale exporting (when last_attempt_at < staleBefore).
|
|
CREATE INDEX IF NOT EXISTS idx_question_table_exports_claim
|
|
ON question_table_exports (status, next_attempt_at, created_at, id)
|
|
WHERE status IN ('pending', 'export_failed')
|
|
OR (status = 'exporting' AND last_attempt_at IS NOT NULL);
|
|
|
|
-- 4. Index for stale exporting recovery
|
|
CREATE INDEX IF NOT EXISTS idx_question_table_exports_stale
|
|
ON question_table_exports (status, last_attempt_at)
|
|
WHERE status = 'exporting'; |