root
4b09a34748
Replaces the scheduler-polled KnowledgeDeletedCleanupWorker with an event-driven KnowledgeDeletedCleanupEventWorker in tenant-api; cleanup events are now enqueued transactionally at delete time instead of swept periodically. Also fixes statement_timeout parameterization in MonitoringRetentionWorker and propagates errors from cleanupDeletedKnowledgeItem. Co-Authored-By: Claude Sonnet 4.6 (1M context) <noreply@anthropic.com>
29 lines
1.2 KiB
SQL
29 lines
1.2 KiB
SQL
CREATE TABLE IF NOT EXISTS knowledge_deleted_cleanup_events (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
tenant_id BIGINT NOT NULL,
|
|
knowledge_item_id BIGINT NOT NULL,
|
|
status VARCHAR(20) NOT NULL DEFAULT 'pending',
|
|
attempt_count INT NOT NULL DEFAULT 0,
|
|
available_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
locked_by TEXT,
|
|
locked_at TIMESTAMPTZ,
|
|
last_error TEXT,
|
|
completed_at TIMESTAMPTZ,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
CHECK (status IN ('pending', 'processing', 'completed', 'failed')),
|
|
CHECK (attempt_count >= 0)
|
|
);
|
|
|
|
CREATE UNIQUE INDEX IF NOT EXISTS uk_knowledge_deleted_cleanup_open
|
|
ON knowledge_deleted_cleanup_events (tenant_id, knowledge_item_id)
|
|
WHERE completed_at IS NULL;
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_knowledge_deleted_cleanup_pending
|
|
ON knowledge_deleted_cleanup_events (available_at, id)
|
|
WHERE completed_at IS NULL AND status IN ('pending', 'failed');
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_knowledge_deleted_cleanup_processing
|
|
ON knowledge_deleted_cleanup_events (locked_at, id)
|
|
WHERE completed_at IS NULL AND status = 'processing';
|