18 lines
799 B
MySQL
18 lines
799 B
MySQL
|
-- AP1.14: Plan-Snapshots (Plan-Revision) — Soll-Zustand getrennt von Ist
|
||
|
|
|
||
|
|
CREATE TABLE roadmap_plan_snapshots (
|
||
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
||
|
|
tenant_id UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
|
||
|
|
initiative_id UUID NOT NULL REFERENCES initiatives(id) ON DELETE CASCADE,
|
||
|
|
roadmap_id UUID NOT NULL REFERENCES roadmaps(id) ON DELETE CASCADE,
|
||
|
|
revision_number INT NOT NULL,
|
||
|
|
reason TEXT NOT NULL DEFAULT '',
|
||
|
|
plan_payload JSONB NOT NULL,
|
||
|
|
created_by_user_id UUID NULL REFERENCES users(id) ON DELETE SET NULL,
|
||
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
||
|
|
UNIQUE (tenant_id, initiative_id, revision_number)
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE INDEX idx_roadmap_plan_snapshots_initiative
|
||
|
|
ON roadmap_plan_snapshots(tenant_id, initiative_id, created_at DESC);
|