42 lines
1.6 KiB
MySQL
42 lines
1.6 KiB
MySQL
|
-- AP1.0: Steering Foundation — steering_contexts
|
||
|
|
|
||
|
|
CREATE TABLE steering_contexts (
|
||
|
|
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,
|
||
|
|
method_key VARCHAR(64) NOT NULL DEFAULT 'generic_operating',
|
||
|
|
method_version VARCHAR(32) NOT NULL DEFAULT '0.1.0',
|
||
|
|
lifecycle_state VARCHAR(64) NOT NULL DEFAULT 'intake',
|
||
|
|
lifecycle_metadata JSONB NOT NULL DEFAULT '{}',
|
||
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
||
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
||
|
|
UNIQUE (tenant_id, initiative_id)
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE INDEX idx_steering_contexts_tenant ON steering_contexts(tenant_id);
|
||
|
|
CREATE INDEX idx_steering_contexts_initiative ON steering_contexts(tenant_id, initiative_id);
|
||
|
|
|
||
|
|
INSERT INTO steering_contexts (tenant_id, initiative_id, lifecycle_state)
|
||
|
|
SELECT
|
||
|
|
i.tenant_id,
|
||
|
|
i.id,
|
||
|
|
CASE
|
||
|
|
WHEN i.status IN ('completed', 'archived') THEN 'closure'
|
||
|
|
WHEN EXISTS (
|
||
|
|
SELECT 1 FROM actions a
|
||
|
|
WHERE a.initiative_id = i.id
|
||
|
|
AND a.tenant_id = i.tenant_id
|
||
|
|
AND a.status IN (
|
||
|
|
'open', 'ready', 'in_progress', 'blocked', 'review_required'
|
||
|
|
)
|
||
|
|
) THEN 'action_selection'
|
||
|
|
WHEN EXISTS (
|
||
|
|
SELECT 1 FROM backlog_items bi
|
||
|
|
WHERE bi.initiative_id = i.id
|
||
|
|
AND bi.tenant_id = i.tenant_id
|
||
|
|
AND bi.status IN ('new', 'triaged', 'accepted')
|
||
|
|
) THEN 'structure_setup'
|
||
|
|
ELSE 'planning'
|
||
|
|
END
|
||
|
|
FROM initiatives i;
|