root
7b4d7ccf68
Provide a unified ops console for inspecting, retrying and cancelling jobs across generation, template/kol assist, knowledge parse, desktop publish/task, compliance review and monitoring collect sources. Wires RabbitMQ for retry republish and consolidates the desktop_publish_jobs columns into the base migration. Co-Authored-By: Claude Opus 4.7 (1M context) <noreply@anthropic.com>
88 lines
3.2 KiB
SQL
88 lines
3.2 KiB
SQL
CREATE EXTENSION IF NOT EXISTS pgcrypto;
|
|
|
|
CREATE TABLE IF NOT EXISTS desktop_publish_jobs (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
desktop_id UUID NOT NULL DEFAULT gen_random_uuid(),
|
|
tenant_id BIGINT NOT NULL REFERENCES tenants(id),
|
|
workspace_id BIGINT NOT NULL REFERENCES workspaces(id),
|
|
created_by_user_id BIGINT NOT NULL REFERENCES users(id),
|
|
title TEXT NOT NULL,
|
|
content_ref JSONB NOT NULL,
|
|
scheduled_at TIMESTAMPTZ,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
article_id BIGINT REFERENCES articles(id),
|
|
article_version_id BIGINT REFERENCES article_versions(id),
|
|
status VARCHAR(32) NOT NULL DEFAULT 'queued',
|
|
compliance_blocked_record_id BIGINT,
|
|
compliance_blocked_at TIMESTAMPTZ,
|
|
compliance_blocked_reason TEXT,
|
|
UNIQUE (desktop_id)
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_desktop_publish_jobs_workspace_created
|
|
ON desktop_publish_jobs (workspace_id, created_at DESC);
|
|
|
|
CREATE TABLE IF NOT EXISTS desktop_tasks (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
desktop_id UUID NOT NULL DEFAULT gen_random_uuid(),
|
|
job_id UUID NOT NULL,
|
|
tenant_id BIGINT NOT NULL REFERENCES tenants(id),
|
|
workspace_id BIGINT NOT NULL REFERENCES workspaces(id),
|
|
target_account_id UUID NOT NULL REFERENCES platform_accounts(desktop_id),
|
|
target_client_id UUID NOT NULL REFERENCES desktop_clients(id),
|
|
platform_id VARCHAR(64) NOT NULL REFERENCES media_platforms(platform_id),
|
|
kind TEXT NOT NULL,
|
|
payload JSONB NOT NULL,
|
|
status TEXT NOT NULL,
|
|
dedup_key TEXT,
|
|
active_attempt_id UUID,
|
|
lease_token_hash BYTEA,
|
|
lease_expires_at TIMESTAMPTZ,
|
|
attempts INT NOT NULL DEFAULT 0,
|
|
result JSONB,
|
|
error JSONB,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
UNIQUE (desktop_id)
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_desktop_tasks_job
|
|
ON desktop_tasks (job_id);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_desktop_tasks_target_client_status
|
|
ON desktop_tasks (target_client_id, status)
|
|
WHERE status IN ('queued', 'in_progress');
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_desktop_tasks_workspace_status_created
|
|
ON desktop_tasks (workspace_id, status, created_at DESC);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_desktop_tasks_target_account
|
|
ON desktop_tasks (target_account_id);
|
|
|
|
CREATE TABLE IF NOT EXISTS desktop_task_attempts (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
desktop_id UUID NOT NULL DEFAULT gen_random_uuid(),
|
|
task_id UUID NOT NULL REFERENCES desktop_tasks(desktop_id) ON DELETE CASCADE,
|
|
client_id UUID NOT NULL REFERENCES desktop_clients(id),
|
|
lease_token_hash BYTEA,
|
|
started_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
ended_at TIMESTAMPTZ,
|
|
final_status TEXT,
|
|
error JSONB,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
UNIQUE (desktop_id)
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_desktop_task_attempts_task_started
|
|
ON desktop_task_attempts (task_id, started_at DESC);
|
|
|
|
CREATE TABLE IF NOT EXISTS desktop_task_traces (
|
|
task_id UUID NOT NULL REFERENCES desktop_tasks(desktop_id) ON DELETE CASCADE,
|
|
attempt_id UUID,
|
|
size BIGINT,
|
|
uploaded_at TIMESTAMPTZ,
|
|
object_key TEXT,
|
|
PRIMARY KEY (task_id, attempt_id)
|
|
);
|