102 lines
6.2 KiB
PL/PgSQL
102 lines
6.2 KiB
PL/PgSQL
SET search_path TO atlas, public;
|
|
|
|
-- 套图绑定一个 SKU；资料与逐张方案是本次任务的不可变快照。
|
|
CREATE TABLE product_image_suites (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
workspace_id UUID NOT NULL REFERENCES workspaces(id) ON DELETE RESTRICT,
|
|
product_id UUID NOT NULL,
|
|
variant_id UUID NOT NULL,
|
|
purpose TEXT NOT NULL CHECK (purpose IN ('plan', 'generate')),
|
|
status TEXT NOT NULL DEFAULT 'queued' CHECK (status IN ('queued', 'running', 'succeeded', 'partial', 'failed', 'cancelled')),
|
|
input JSONB NOT NULL CHECK (jsonb_typeof(input) = 'object'),
|
|
shots JSONB NOT NULL DEFAULT '[]' CHECK (jsonb_typeof(shots) = 'array' AND jsonb_array_length(shots) <= 20),
|
|
error_code TEXT,
|
|
model_name TEXT,
|
|
prompt_version TEXT NOT NULL DEFAULT 'image-suite-v1',
|
|
token_usage INTEGER,
|
|
created_by_user_id UUID REFERENCES users(id) ON DELETE SET NULL,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
completed_at TIMESTAMPTZ,
|
|
FOREIGN KEY (workspace_id, variant_id, product_id) REFERENCES product_variants(workspace_id, id, product_id) ON DELETE RESTRICT,
|
|
UNIQUE (workspace_id, id),
|
|
UNIQUE (workspace_id, id, product_id),
|
|
CHECK (purpose <> 'generate' OR jsonb_array_length(shots) >= 1),
|
|
CHECK (jsonb_array_length(input -> 'reference_asset_ids') BETWEEN 1 AND 8)
|
|
);
|
|
CREATE INDEX ix_product_image_suites_recent ON product_image_suites(workspace_id, product_id, created_at DESC, id DESC);
|
|
|
|
-- 每个底图槽位强制引用当前工作空间正式素材，不能借用外部租户 ID。
|
|
CREATE TABLE product_image_suite_references (
|
|
workspace_id UUID NOT NULL,
|
|
suite_id UUID NOT NULL,
|
|
asset_id UUID NOT NULL,
|
|
position INTEGER NOT NULL CHECK (position BETWEEN 0 AND 7),
|
|
PRIMARY KEY (workspace_id, suite_id, asset_id),
|
|
UNIQUE (workspace_id, suite_id, position),
|
|
FOREIGN KEY (workspace_id, suite_id) REFERENCES product_image_suites(workspace_id, id) ON DELETE CASCADE,
|
|
FOREIGN KEY (workspace_id, asset_id) REFERENCES media_assets(workspace_id, id) ON DELETE RESTRICT
|
|
);
|
|
|
|
ALTER TABLE media_ai_edits ADD COLUMN suite_id UUID,
|
|
ADD COLUMN suite_shot_id UUID,
|
|
ADD COLUMN suite_position INTEGER,
|
|
ADD CONSTRAINT media_ai_edits_suite_fk FOREIGN KEY (workspace_id, suite_id, product_id)
|
|
REFERENCES product_image_suites(workspace_id, id, product_id) ON DELETE RESTRICT,
|
|
ADD CONSTRAINT media_ai_edits_suite_shape CHECK (
|
|
(suite_id IS NULL AND suite_shot_id IS NULL AND suite_position IS NULL) OR
|
|
(suite_id IS NOT NULL AND product_id IS NOT NULL AND suite_shot_id IS NOT NULL AND suite_position BETWEEN 0 AND 19)),
|
|
ADD CONSTRAINT media_ai_edits_suite_shot_unique UNIQUE (workspace_id, suite_id, suite_shot_id),
|
|
ADD CONSTRAINT media_ai_edits_suite_position_unique UNIQUE (workspace_id, suite_id, suite_position);
|
|
|
|
CREATE FUNCTION guard_image_suite_references() RETURNS TRIGGER LANGUAGE plpgsql AS $$
|
|
BEGIN
|
|
IF NEW.suite_id IS NOT NULL AND EXISTS (
|
|
SELECT 1 FROM unnest(ARRAY[NEW.source_asset_id] || NEW.reference_asset_ids) AS refs(id)
|
|
WHERE NOT EXISTS (SELECT 1 FROM product_image_suite_references r
|
|
WHERE r.workspace_id = NEW.workspace_id AND r.suite_id = NEW.suite_id AND r.asset_id = refs.id)
|
|
) THEN
|
|
RAISE EXCEPTION '套图子任务底图必须来自当前任务底图库' USING ERRCODE = '23514';
|
|
END IF;
|
|
RETURN NEW;
|
|
END $$;
|
|
CREATE TRIGGER media_ai_edits_suite_references BEFORE INSERT OR UPDATE OF source_asset_id, reference_asset_ids, suite_id
|
|
ON media_ai_edits FOR EACH ROW EXECUTE FUNCTION guard_image_suite_references();
|
|
|
|
-- 汇总依赖数据库子任务事实；重投递不会增加成功计数，也不会覆盖取消的整套。
|
|
CREATE FUNCTION refresh_image_suite_status() RETURNS TRIGGER LANGUAGE plpgsql AS $$
|
|
DECLARE
|
|
parent product_image_suites%ROWTYPE;
|
|
count_all INTEGER;
|
|
count_done INTEGER;
|
|
count_active INTEGER;
|
|
next_status TEXT;
|
|
BEGIN
|
|
IF NEW.suite_id IS NULL THEN RETURN NEW; END IF;
|
|
SELECT * INTO parent FROM product_image_suites WHERE workspace_id = NEW.workspace_id AND id = NEW.suite_id FOR UPDATE;
|
|
IF parent.status = 'cancelled' THEN RETURN NEW; END IF;
|
|
SELECT count(*), count(*) FILTER (WHERE status = 'succeeded'), count(*) FILTER (WHERE status IN ('queued', 'running'))
|
|
INTO count_all, count_done, count_active FROM media_ai_edits WHERE workspace_id = NEW.workspace_id AND suite_id = NEW.suite_id;
|
|
next_status := CASE WHEN count_active > 0 THEN 'running' WHEN count_done = count_all THEN 'succeeded'
|
|
WHEN count_done > 0 THEN 'partial' ELSE 'failed' END;
|
|
UPDATE product_image_suites SET status = next_status,
|
|
completed_at = CASE WHEN count_active = 0 THEN now() ELSE NULL END,
|
|
error_code = CASE WHEN next_status IN ('partial', 'failed') THEN 'AI_SUITE_IMAGE_FAILED' ELSE NULL END
|
|
WHERE workspace_id = NEW.workspace_id AND id = NEW.suite_id;
|
|
UPDATE operations SET status = CASE WHEN next_status = 'partial' THEN 'failed' ELSE next_status END,
|
|
started_at = coalesce(started_at, now()), completed_at = CASE WHEN count_active = 0 THEN now() ELSE NULL END,
|
|
error_code = CASE WHEN next_status IN ('partial', 'failed') THEN 'AI_SUITE_IMAGE_FAILED' ELSE NULL END,
|
|
error_message = CASE WHEN next_status IN ('partial', 'failed') THEN '部分图片未完成，可重试失败项' ELSE NULL END,
|
|
result = jsonb_build_object('completed', count_done, 'total', count_all)
|
|
WHERE workspace_id = NEW.workspace_id AND id = NEW.suite_id;
|
|
RETURN NEW;
|
|
END $$;
|
|
CREATE TRIGGER media_ai_edits_suite_progress AFTER UPDATE OF status ON media_ai_edits
|
|
FOR EACH ROW WHEN (OLD.status IS DISTINCT FROM NEW.status) EXECUTE FUNCTION refresh_image_suite_status();
|
|
|
|
ALTER TABLE product_image_suites ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE product_image_suites FORCE ROW LEVEL SECURITY;
|
|
CREATE POLICY workspace_isolation ON product_image_suites USING (workspace_id = app_current_workspace_id()) WITH CHECK (workspace_id = app_current_workspace_id());
|
|
ALTER TABLE product_image_suite_references ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE product_image_suite_references FORCE ROW LEVEL SECURITY;
|
|
CREATE POLICY workspace_isolation ON product_image_suite_references USING (workspace_id = app_current_workspace_id()) WITH CHECK (workspace_id = app_current_workspace_id());
|