姚章浩
20 lines
780 B
SQL
20 lines
780 B
SQL
-- 000004_delivery_audit: delivery audit columns on messages
|
|
-- Adds delivered_at, delivery_method, provider_message_id, failure_code
|
|
-- with a tenant+status+updated_at query index.
|
|
|
|
ALTER TABLE messages
|
|
ADD COLUMN IF NOT EXISTS delivered_at TIMESTAMPTZ;
|
|
|
|
ALTER TABLE messages
|
|
ADD COLUMN IF NOT EXISTS delivery_method TEXT NOT NULL DEFAULT ''
|
|
CHECK (delivery_method IN ('', 'card', 'markdown'));
|
|
|
|
ALTER TABLE messages
|
|
ADD COLUMN IF NOT EXISTS provider_message_id TEXT NOT NULL DEFAULT '';
|
|
|
|
ALTER TABLE messages
|
|
ADD COLUMN IF NOT EXISTS failure_code TEXT NOT NULL DEFAULT ''
|
|
CHECK (failure_code IN ('', 'channel_delivery_failed'));
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_messages_tenant_status_updated
|
|
ON messages (tenant_key, status, updated_at DESC); |