-- ============================================================ -- Attachments: metadata, variants, jobs/outbox, GC -- ============================================================ CREATE TABLE IF NOT EXISTS attachments ( id UUID PRIMARY KEY, chat_id UUID NOT NULL REFERENCES chats(id) ON DELETE CASCADE, owner_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE, object_key TEXT NOT NULL UNIQUE, filename TEXT NOT NULL, content_type TEXT NOT NULL, size_bytes BIGINT NOT NULL CHECK (size_bytes >= 0 AND size_bytes <= 10485760), sha256 TEXT NOT NULL CHECK (sha256 ~ '^[a-f0-9]{64}$'), width INTEGER NOT NULL CHECK (width >= 0), height INTEGER NOT NULL CHECK (height >= 0), status TEXT NOT NULL DEFAULT 'queued' CHECK (status IN ('queued','processing','ready','failed','pending')), error_code TEXT, -- поля из 0006 pipeline_version INTEGER NOT NULL DEFAULT 1, staging_key TEXT, ready_at TIMESTAMPTZ, failed_at TIMESTAMPTZ, created_at TIMESTAMPTZ NOT NULL DEFAULT now(), updated_at TIMESTAMPTZ NOT NULL DEFAULT now() ); CREATE INDEX IF NOT EXISTS idx_attachments_chat ON attachments (chat_id, created_at, id); CREATE INDEX IF NOT EXISTS idx_attachments_owner ON attachments (owner_id); -- Для reconciler/поиска «зависших» pending-записей: CREATE INDEX IF NOT EXISTS idx_attachments_pending ON attachments (created_at) WHERE status <> 'ready'; -- Связь сообщение ↔ вложения. FK на элементы массива Postgres не умеет, -- целостность — на уровне приложения (или триггера, если нужно). ALTER TABLE messages ADD COLUMN IF NOT EXISTS attachment_ids UUID[] NOT NULL DEFAULT '{}'; -- Журнал компенсации: объекты S3, которые могли остаться без метаданных. CREATE TABLE IF NOT EXISTS attachment_gc ( object_key TEXT PRIMARY KEY, reason TEXT NOT NULL, created_at TIMESTAMPTZ NOT NULL DEFAULT now() ); -- Idempotency эффекта: (attachment_id, pipeline_version, variant) -- content_type без CHECK: варианты могут быть webp и т.п. -- sha256 — text+CHECK, чтобы не ловить padding от char(64). CREATE TABLE IF NOT EXISTS attachment_variants ( attachment_id UUID NOT NULL REFERENCES attachments(id) ON DELETE CASCADE, pipeline_version INTEGER NOT NULL, variant TEXT NOT NULL, object_key TEXT NOT NULL, content_type TEXT NOT NULL, size_bytes BIGINT NOT NULL, width INTEGER NOT NULL, height INTEGER NOT NULL, sha256 TEXT NOT NULL CHECK (sha256 ~ '^[a-f0-9]{64}$'), created_at TIMESTAMPTZ NOT NULL DEFAULT now(), PRIMARY KEY (attachment_id, pipeline_version, variant) ); -- Outbox + очередь job-ов CREATE TABLE IF NOT EXISTS attachment_jobs ( job_id UUID PRIMARY KEY, attachment_id UUID NOT NULL REFERENCES attachments(id) ON DELETE CASCADE, pipeline_version INTEGER NOT NULL, attempt INTEGER NOT NULL DEFAULT 1, status TEXT NOT NULL DEFAULT 'queued' CHECK (status IN ('queued','processing','done','failed')), -- outbox published_at TIMESTAMPTZ, publish_attempts INTEGER NOT NULL DEFAULT 0, -- lease lease_owner TEXT, lease_expires_at TIMESTAMPTZ, -- retry next_attempt_at TIMESTAMPTZ NOT NULL DEFAULT now(), last_error_code TEXT, created_at TIMESTAMPTZ NOT NULL DEFAULT now(), updated_at TIMESTAMPTZ NOT NULL DEFAULT now(), UNIQUE (attachment_id, pipeline_version) -- одна логическая работа на версию ); CREATE INDEX IF NOT EXISTS idx_attachment_jobs_unpublished ON attachment_jobs (next_attempt_at) WHERE published_at IS NULL AND status = 'queued'; CREATE INDEX IF NOT EXISTS idx_attachment_jobs_lease ON attachment_jobs (lease_expires_at) WHERE status = 'processing';